0

Using Postgresql in clustered database (stado) on two nodes, I want to test this query:

select id,position,timestamp from table t1 WHERE id!=0 AND ST_Intersects ((Select ST_Buffer_Meters(ST_SetSRID(ST_MakePoint(61.4019,15.218205), 4326) ,1160006)),position) AND timestamp Between '2013-10-01' and '2013-12-30';

When I run it in command line or psql (on coordinator), I get this error:

Encountered ")" at line 1, column 171.

While other sql commands (insert, update, select... etc) are working fine. also Geometry columns seems okay in the table so i don't think there is a problem with installing PostGIS.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Shadin
  • 1,867
  • 5
  • 26
  • 37
  • This really is me grasping at straws, but I have seen rogue spaces cause problems when calling functions: `ST_Intersects (`. – MatBailie Apr 06 '14 at 19:45
  • what should i do to resolve it? – Shadin Apr 06 '14 at 20:13
  • Try removing the space from between `ST_Intersects` and the `(`? – MatBailie Apr 06 '14 at 20:29
  • thanks but just tried it. didn't work. – Shadin Apr 06 '14 at 20:54
  • ST_Buffer_Meters is not an official function, though looking at it, is is composed of other Postgis functions. I would recommend using ST_Transform to convert the lat/lon to whatever local projected coordinate system you are using, and then you can do distance in meters. And don't post examples with non-standard functions :-) – John Powell Apr 07 '14 at 14:39

2 Answers2

2

Generally-speaking, don't buffer a geometry to do a proximity search. With the above attempt, it is for just one point geometry, but in other queries you could be potentially buffering all the geometries of a table, which would make the query expensive since it would need to create new geometries and would not be able to use indexes. Use ST_DWithin instead.

ST_DWithin with geometry types will use the same distance units as the the spatial reference system. So for SRID=4326, this is in degrees, which is not helpful in any way. However, if position is a geography type, ST_DWithin will use distance arguments in meters, which is much more useful. So the WHERE filter would look like:

WHERE id <> 0
  AND ST_DWithin(ST_MakePoint(61.4019, 15.218205)::geography, position, 1160006)
...

This will do a proximity search of positions that are 1160006 m or 1160 km from the queried location (which, by the way is not in Sweden, if that's where you were thinking). If position is a geometry type, you can either consider changing the type, or do a cast (position::geography) or an index of that cast operation.

Mike T
  • 41,085
  • 18
  • 152
  • 203
1

I can't find the function ST_Buffer_Meters() in the PostGis manual, only ST_Buffer()

Either way, I can't imagine any function would require a subquery as parameter. Try instead:

SELECT id,position,timestamp
FROM   table t1
WHERE  id <> 0
AND    ST_Intersects(ST_Buffer_Meters(ST_SetSRID(
                     ST_MakePoint(61.4019, 15.218205), 4326), 1160006), position)
AND    timestamp BETWEEN '2013-10-01' AND '2013-12-30';

And don't use timestamp as identifier. It's a base type name and a reserved word in SQL.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • It turns out ST_Buffer_Meters is a function someone created, see: st_buffer_meters This seems a bit pointless, when all you need to do is use use ST_Transform to convert to a projected coordinate system when you can then use meters. – John Powell Apr 07 '14 at 14:29