Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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.

share|improve this question
    
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. –  Craig Ringer Apr 1 '13 at 8:05
add comment

1 Answer

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).

share|improve this answer
    
Looking at the command: –  John Mitchell Apr 1 '13 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? –  John Mitchell Apr 1 '13 at 11:51
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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