[GIS] “Operation on two GEOMETRIES with different SRIDs”

postgis

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.

Best Answer

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.

Related Question