2

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

1
  • 1
    If you provide all your SQL, not just a fragment, then a modification could be offered.
    – Vince
    Dec 9, 2013 at 16:52

4 Answers 4

2

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
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
0

Add a constraint to the query that removes the basis feature from consideration.

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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