0

Postgres: How do I get the closest distance for each element within a point coordinate table from a polyline table? And if this is possible what version of Postgres is this doable.

1
  • I'd recommend that you post a table definition, some sample data and some expected results. It'll mean people don't have to construct this data in order to test possible solutions. Apr 1, 2013 at 8:05

1 Answer 1

1

In the absence of any further details on your tables, your best bet is to install PostGIS, which is the spatial-enablement extension to Postgresql. PostGIS 2 requires Postgresql 9.1 or higher. PostGIS 1.x will run on earlier versions of Postgresql but you might as well get the most up to date versions.

Then follow a few tutorials so you can cast your tables as GIS point and line features (unless they are already are like that - it's not clear from your post at the time of writing).

Once you have your data and PostGIS set up, use ST_Distance. You could skip the paragraph above and do it all in the ST_Distance sql command using one of the ST_GeomFromXXX (substitute format for XXX e.g. ST_GeomFromText) but that would mean a more complex SQL command (your call). Just be careful with your projections and coordinate systems (which is why a tutorial is useful if you're not familiar with GIS - which is what I am guessing from your query).

2
  • Looking at the command: Apr 1, 2013 at 11:42
  • Looking at the command:float ST_Distance(geometry g1, geometry g2); and the example:SELECT ST_Distance( ST_GeomFromText('POINT(-72.1235 42.3521)',4326), ST_GeomFromText('LINESTRING(-72.1260 42.45, -72.123 42.1546)', 4326) ); to do it for 2 tables one point and the other polyline would I do it this way? SELECT ST_Distance(select the_geom from pointtable, select the_geom from polylinetable); This would output the distance of all the points from the polyline table. Correct? Apr 1, 2013 at 11:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.