Comments (8)
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?
from wazeccpprocessor.
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.
from wazeccpprocessor.
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.
from wazeccpprocessor.
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
- a simple "create table" query with with a geom_line and a geom_point field in it?
- a "modify table" query with how you'd add empty fields to an existing table for geom_line and a geom_point?
- 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?
from wazeccpprocessor.
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);
from wazeccpprocessor.
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!
from wazeccpprocessor.
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
from wazeccpprocessor.
This is added for the Jams, Alerts, and Coordinates table in latest code.
from wazeccpprocessor.
Related Issues (20)
- Create PowerBI templates around good use cases and save files to repo HOT 1
- Create Tableau integration
- Enrich DB tables with useful fields - Direction, Day of Week HOT 4
- Optimize Data Store for performance HOT 3
- Work to reduce database costs HOT 8
- Optimize how files are stored or processed HOT 1
- Getting ready for Terraform release v2.0 HOT 8
- Create a read-only database user HOT 1
- Support Microsoft Azure within Terraform HOT 4
- Support Google Cloud within Terraform HOT 1
- Your Use Cases HOT 2
- Add type_id to the Alerts table HOT 5
- Create instructions on how to change settings to change an env-dev deployment to an env-prod HOT 1
- Useful SQL Query Collection HOT 2
- Release Versioning - Upgrade docs and new install integration
- Replace link on Project page HOT 1
- Create an import script
- Add local timezone field HOT 1
- Upgrade Node Version for Lambdas HOT 1
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from wazeccpprocessor.