0

I have this query that search for the nearest 5 stations (5km at max) from the location that I'm selecting (com.id):

SELECT est.name as nombre, '5km' AS metodo FROM vectorial.meteorologicasccg est 
JOIN vectorial.dpa_comuna com ON  com.id = :id 
WHERE ST_Contains(ST_Buffer( ST_Centroid( com.thegeom ), 5000 ), st_transform( est.the_geom, 3857 ) )

It works ok. But, in case that I have no results, I will go for the nearest, and I'm trying to do it with this:

SELECT est.name as nombre, 'mascercano' AS metodo, ST_Distance( ST_Centroid( com.thegeom ), st_transform( est.the_geom, 3857 ) ) AS distancia 
FROM vectorial.meteorologicasccg est 
JOIN vectorial.dpa_comuna com ON  com.id = :id 
ORDER BY ST_Distance( ST_Centroid( com.thegeom ), st_transform( est.the_geom, 3857 ) ) ASC LIMIT 1

But I got this error:

"Operation on two GEOMETRIES with different SRIDs"

I'm trying to replicate this:

I have this query, it calculates the minimum distance of the center of a 'radio' search ( a circunference) and some target. It works:

SELECT est.name as nombre, 'mascercano' AS metodo, ST_Distance( ST_Centroid( st_geometryfromtext('POINT(".$center[0]." ".$center[1].")', 3857) ), st_transform( est.the_geom, 3857 ) ) AS distancia 
FROM vectorial.meteorologicasccg est ORDER BY ST_Distance( ST_Centroid( st_geometryfromtext('POINT(".$center[0]." ".$center[1].")', 3857) ), st_transform( est.the_geom, 3857 ) ) ASC LIMIT 1

This one use a different type of search, but it's working.

What I'm doing wrong in the query?

I'm very new to GIS.

  • Now I'm using st_transform( est.the_geom, 32719 )now it works. I said that with 3857 was working but it wasn't, I checked some old code and est.the_geom need to by 32719. I solved it. – pmiranda Mar 2 '17 at 17:10
1

In your second query you did not transform the geometry to the same SRID. Try:

SELECT est.name as nombre, 'mascercano' AS metodo, ST_Distance( ST_Centroid( 
ST_Transform(com.thegeom,3857)), st_transform( est.the_geom, 3857 ) ) AS 
distancia FROM vectorial.meteorologicasccg est  JOIN vectorial.dpa_comuna  com 
ON  com.id = id 
ORDER BY   
ST_Distance(ST_Centroid(ST_Transform(com.thegeom,3587 ), st_transform( 
est.the_geom, 3857 ) ) ASC LIMIT 1

I don't see why you want to join on the ID. Have a look at this page explaining several ways to perform a nearest neighbour join in Postgis. One of the outlined methods is:

SELECT f1.id As ref_id, f1.ref_name, f2.id As knearest_id,  
f2.ref_name As  knearest_name 
FROM foo As f1, foo As f2   
WHERE f1.id = 1 and f1.id <> f2.id 
AND ST_DWithin(f1.the_geom, f2.the_geom, 500)   
ORDER BY ST_Distance(f1.the_geom,f2.the_geom) 
LIMIT 10

Do not forget to add a spatial index on larger data sets and transform your geometry column into a projected coordinate system for the distance to make sense.

1

The problem in your first query is that you are only transforming one of the geoms into a different SRID.

Try replacingWHERE ST_Contains(ST_Buffer( ST_Centroid( com.thegeom ), 5000 ), st_transform( est.the_geom, 3857 ) ) in the first query with

Where ST_Contains(st_transform(ST_Buffer( ST_Centroid(com.thegeom), 5000), 3857), st_transform(est.the_geom, 3857) )

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.