Create geometry fields with line/point data for GIS applications

[CentennialCo]

Is there a way to update a geometry column where there are lat/long columns? I would like to put it into a view and can't use UPDATE in it. Here's what I run manually to create the Geom field in a new column.

`DROP TABLE IF EXISTS waze.wazejams;
CREATE TABLE waze.wazejams AS
SELECT j.speed, j.level, j.length, j.delay, j.city, j.street, j.line, j.pub_utc_date at time zone 'utc' at time zone 'america/denver',j.id
from waze.jams j
ORDER BY j.pub_utc_date DESC;
SELECT AddGeometryColumn ('waze','wazejams','geom_line',4326,'LINESTRING',2);
WITH
lines AS(
   SELECT id,
          ST_MakeLine(
            ST_MakePoint(
              (line -> n ->> 'x')::NUMERIC,
              (line -> n ->> 'y')::NUMERIC
            )
          ) AS geom
   FROM waze.wazejams
   CROSS JOIN generate_series(0, jsonb_array_length(line)) AS n
   GROUP BY id)

 
UPDATE waze.wazejams AS a
SET geom_line = ST_SetSRID(b.geom, 4326)
FROM lines AS b
WHERE a.id = b.id`

[schnuerle]

I think this is a good idea. Can you explain where in the schema you may like to put these fields (eg, what table, or a new table?). And what is your reasoning - to use Postgres to the best of its ability with the its geom capabilities?

[CentennialCo]

Maybe just having the column populate geometry in the the 'coordinate' table in a WGS84 SRID would work best? I don't really have a specific answer or solution. Being able to join tables without creating a column each time is what kind of result I was looking for. And if there's a way to pull this into QGIS without creating a geometry column that would work as well.

[CentennialCo]

Here is a high-level proposal of what we were thinking would add value to the Waze WARP product. Currently, WARP populates tables with latitude and longitude in double precision fields for points under the coordinates table. It also uses a json data type in the 'line' column in the 'jams' table. The result we're looking for is to pull these tables into QGIS or ArcGIS. In order to pull these tables, they need to read from a geom column.
My proposal for the enhancement:

• add a geom column named geom_line in 'jams' table.
• add a geom column named geom_point in 'coordinates' table.

From here anyone could pull data directly into these programs or publish the layers to a service.

[schnuerle]

I think we are going to go with geom_line in 'jams' and geom_point in 'alerts'.

@CentennialCo Can you enumerate here the SQL code for

1. a simple "create table" query with with a geom_line and a geom_point field in it?
2. a "modify table" query with how you'd add empty fields to an existing table for geom_line and a geom_point?
3. an "update table" query for how you'd update the data in each geom_line and a geom_point field with data from the table?

[CentennialCo]

Both of these are going to have extra columns, but it's basically taking the coordinates and converting them to a geometry. Here is the query for points. I tried to Bold what is important.
Here is a table that updates the geom_point:

DROP TABLE IF EXISTS waze.wazecombine;
CREATE TABLE waze.wazecombine AS
SELECT DISTINCT ON (a.uuid) uuid,a.pub_utc_date, a.type, a.subtype, a.street,
r."name", c.latitude, c.longitude
from waze.alerts a
join waze.roads r on a.road_type = r.value
join waze.coordinates c on a.id = c.alert_id
ORDER BY a.uuid;
--Updates the Geometry
SELECT AddGeometryColumn ('waze','wazecombine','geom',4326,'POINT',2);
--Updates the geometry
UPDATE waze.wazecombine SET geom = ST_SetSRID(ST_MakePoint(longitude, latitude), 4326);

[schnuerle]

For the Create Table part, I'd like to see how you would create it on its own, not connected to other tables or data from other tables. Eg:

create table jamsnew (uuid serial, name varchar, geom geometry(POINT, 4326));

For updating, I see now that something like this has been depreciated for editing a table structure for geometry:

ALTER TABLE some_table ADD COLUMN geom geometry(Point,4326)

in favor of:

SELECT AddGeometryColumn ('waze','wazecombine','geom',4326,'POINT',2);

Bottom line, thanks, this is good!

[schnuerle]

For Alerts I can do this (I think, not connected to DB now):

UPDATE waze.alerts SET geom_point = ST_SetSRID(ST_MakeLine(longitude, latitude), 4326);

How do you do that for Jams and linestring? Is this too complex?

UPDATE waze.jams SET geom_line = 
          ST_MakeLine(
            ST_MakePoint(
              (line -> n ->> 'x')::NUMERIC,
              (line -> n ->> 'y')::NUMERIC
            )
          ) AS geom_line
   FROM waze.wazejams
   CROSS JOIN generate_series(0, jsonb_array_length(line)) AS n

[schnuerle]

This is added for the Jams, Alerts, and Coordinates table in latest code.