CODEWITHSUNDEEP
postgresqlpostgis
Ramprasad
Ramprasad

Reputation: 8071

Query to find distance between a point column and a point in PostGis

I am using PostGis for Location based calculations in my Application. In a Table i have a column called 'location' in geography type(Point(lon lat))...Like this number of rows present in the Table. I want to pass a point(Point(lon lat)) and check distance between this point(i passed) and location column in all rows....and if distance is less than 5 m....it will return the name of the point.How to query this.

Upvotes: 1

Views: 2055

Answers (2)

Ramprasad
Ramprasad

Reputation: 8071

Great.Thank You. It works fine in database. I have following code from PHP..it returs like, Query Failed:
$locationresult=pg_query($con,"SELECT id,name FROM gps.locationnames WHERE ST_DWithin(location,ST_GeographyFromText('POINT(lon lat)'),500)") or die ('Query Failed:'.pg_last_error($con));

What is the problem here..

Upvotes: -3

Paco Valdez
Paco Valdez

Reputation: 1975

Assuming that your srid of your data is 4326 the query you are looking for is:

SELECT the_geom FROM mytable WHERE ST_DWithin(the_geom,ST_GeomFromEWKT("srid=4326;POINT(lon lat)"), 0.0008);

Note that the units(0.0008) in ST_DWithin are in the same units of your projection, in the 4326 case they are degrees. If your projection data is in meters, you will be able to use meters.

For a production application you should use geometry types, is faster. From a stackoverflow previous question:

Short Answer: geography is a new data type that supports long range distances measurements. If you use geography -- you don't need to learn much about planar coordinate systems. Geography is generally best if all you care about is measuring distances and lengths and you have data from all over the world. Geometry datatype is an older data type that has many functions supporting it and enjoys great support from third party tools. Its best if you are pretty comfortable with spatial reference systems or you are dealing with localized data where all your data fits in a single spatial reference system (SRID), or you need to do a lot of spatial processing. Refer to Section 8.8, “PostGIS Function Support Matrix” to see what is currently supported and what is not.

Upvotes: 2

Related Questions