CODEWITHSUNDEEP
sqlgispostgis
thanhtv
thanhtv

Reputation: 333

How to use ST_DWithin to find near point?

This is the first time I create a GIS query. In one table of my database, there is a column with point type. Each record is one ATM machine. I want to write a query to get ATM machines that near by my location within 1km range.How to use ST_DWithin in SQL query to find record?

Upvotes: 4

Views: 5556

Answers (2)

Alexander Palamarchuk
Alexander Palamarchuk

Reputation: 879

SELECT *
  FROM atm_finder
  WHERE ST_Distance(ST_Transform(ST_GeomFromText('POINT([Lon] [Lat])',4326),26986),ST_Transform(location,26986)) <= 1000

Where [Lon] & [Lat] - GPS coordinates of the point. But so far as you used POINT type at first use: 

SELECT AddGeometryColumn('atm_finder', 'location', 4326, 'POINT', 2);

Of course before that you should rename the field 'location' (in order to not lose the data) and fill the new one with this data.

Upvotes: 4

Rapha&#235;l Althaus
Rapha&#235;l Althaus

Reputation: 60503

I don't see well how ST_DWithin could be used with two points...

Something like that . 

select atm.id
from atm_finder atm
where ST_Distance(<your location>, atm.location) <= 1000 

//1000 = meters, works fine with geography types
//have to check your projection with geometry types

The way your table is created looks strange to me, by the way...

Upvotes: 0

Related Questions