I have a query that calculates the distance away from a given point, however currently my query returns the point I am actually querying a distance for. How would I prevent site 1 from showing in my results?
Site 1 = 0.00km site 2 = 0.15km site 3 = 3.5km
st_distance(the_geom, st_geomfromtext($geomdistanceaway,27700))::numeric(10,2) AS distanceaway from distancetbl ORDER BY distanceaway asc limit 3
Use OFFSET :
OFFSET says to skip that many rows before beginning to return rows. OFFSET 0 is the same as omitting the OFFSET clause, and LIMIT NULL is the same as omitting the LIMIT clause.
If you want to skip the first result :
st_distance(the_geom, st_geomfromtext($geomdistanceaway,27700))::numeric(10,2) AS distanceaway from distancetbl ORDER BY distanceaway asc limit 3 OFFSET 1
A self join would get you what you need.
SELECT a.id As FromID
,b.id As ToID
, ST_Distance(a.the_geom, b.the_geom)::numeric(10.2) As DistanceAway
FROM distancetbl a, distancetbl b
WHERE a.id <> b.id
ORDER BY distanceaway
LIMIT 3
Or wrap your current query in a subquery and use a where clause to filter out your 0 distance values
SELECT * FROM (
st_distance(the_geom, st_geomfromtext($geomdistanceaway,27700))::numeric(10,2) AS
distanceaway from distancetbl ORDER BY distanceaway asc limit 3) x
WHERE distanceaway > 0
Add a constraint to the query that removes the basis feature from consideration.
There are at least a couple of ways. The quick and dirty way is to use your current query as a subquery:
SELECT * FROM
[your subquery] AS distance_table
WHERE distanceaway > 0
Another way is to reference "distancetbl" twice with aliases in your FROM clause and then test against matching site id in your WHERE clause:
...
FROM "distancetbl" t1, "distancetbl" t2
...
WHERE NOT t1.site_id = t2.site_id
The second method would require further modification to your query.
