[GIS] Find distance from a point to linestring

distancesql server

I have table (shapetable) with my coastline shapefile loaded. I have another table (pointtable) with lat/long points. Wondering how would I go about writing a SQL query to find the distance between the lat/long points and the shapefile. The shapetable has a geography type attribute, from my reading it appears that geometry type linestrings and geography are one and the same.

Appreciate the help

EDIT: Being relatively a newbie at this, I'm not using PostGIS. I am using SQL Server 2008. I imported the shapefile using a shape2sql utility. The table has a bunch of shapefile attributes and also the geography data type. If I understand this correctly, the geography binary data is nothing but linestring of geometry data type (I'd verify this by running a stgeoemtrytype() on the column)

I have another table with a bunch of lat/long. I want to implement a solution where by I want to get distance from the shapefile, using stdistance. One confusing part I found in going through a bunch of documents was that, given a geography data type, how could I find out the linestring coordinates.

Best Answer

First of all you need to make sure that both your coastline features and points are in the GEOGRAPHY datatype and that the coordinates are lat/longs.

Alternatively make sure all your features are in the GEOMETRY datatype and all coordinates are projected (to Mercator, a National Grid etc.).

Then using the STDistance method you should be able to use a query similar to: 

SELECT pointtable.ID,
MIN(pointtable.GEOG.STDistance(shapetable.GEOG)) as MinDistance
FROM pointtable, shapetable
GROUP BY pointtable.ID
  • GEOG field names should be replaced with the field names containing the GEOGRAPHY data.
  • ID field names should be replaced with the appropriate ID field for the table.

If you want to see the actual string represenations of a GEOGRAPHY field use SQL such as: 

SELECT shapetable.ID,
shapetable.GEOG.STAsText()
FROM shapetable
Related Question