Hi Ralph,
Thanks so very much for your help.
In my PG 8.3 PostGIS 1.3.3 setup when I try to run explain on the nested
select version of my query I get:
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop IN Join (cost=0.00..2.69 rows=1 width=358)
Join Filter: (gvt.id = gvt1.id)
-> Seq Scan on geometryvaluetype gvt (cost=0.00..1.33 rows=1
width=358)
Filter: (st_srid(geometry) = 4326)
-> Seq Scan on geometryvaluetype gvt1 (cost=0.00..1.27 rows=7 width=8)
Filter: within(gvt1.geometry,
'0103000020E6100000010000000500000000000000000059C00000000000000000000000000000000000000000000000000000000000000000000000000000594000000000000059C0000000000000594000000000000059C00000000000000000'::geometry)
(6 rows)
Based on above it seems I am first filtering on SRID - No?
What am I missing?
Can you tell me what my query should look like to make it work?
On 01/14/2011 10:12 AM, Ralf Suhr wrote:
Hi Farrukh,
you have to first check for the right srid before you can compare the bbox. If
you use the SQL command EXPLAIN for your query yyou can see what will be done.
SELECT *
FROM GeometryValueType gvt
WHERE gvt.id IN (
SELECT gvt1.id
FROM GeometryValueType gvt1
WHERE ST_SRID(gvt.geometry) = 4326
) AND Within(gvt1.geometry, ST_GeomFromText('POLYGON((-100 0, 0 0, 0
100, -100 100, -100 0))', 4326)) = true;
Gr
Ralf
Am Freitag 14 Januar 2011, 15:55:31 schrieb Farrukh Najmi:
BTW, using nested SELECT did not work either as shown in follow query:
SELECT * FROM GeometryValueType gvt WHERE
ST_SRID(gvt.geometry) = 4326 AND gvt.id IN (
SELECT gvt1.id FROM GeometryValueType gvt1 WHERE
within(gvt1.geometry, ST_GeomFromText('POLYGON((-100 0, 0 0, 0
100, -100 100, -100 0))', 4326)) = true
)
It again gives the "Operation on mixed SRID geometries" error :-(
It seems to me that surely this should be doable. If it is not doable,
do folks think that is an issue that should be tracked?
On 01/14/2011 09:49 AM, Farrukh Najmi wrote:
Hi Ture,
Thanks for the advice. Unfortunately WITH is not supported in postgres
8.3. So I could not try it out.
Any other ideas?
On 01/14/2011 12:38 AM, Ture Pålsson wrote:
2011/1/14 Farrukh Najmi<farr...@wellfleetsoftware.com>:
--Gives Error: Operation on mixed geometries
SELECT * FROM GeometryValueType gvt WHERE ( ST_SRID(gvt.geometry) =
4326
AND
within(gvt.geometry, ST_GeomFromText('POLYGON((-100 0, 0 0, 0
100, -100
100, -100 0))', 4326)) = true )
Does SQL guarantee left-to-right evaluation and short-circuiting of
AND checks the way that C does? I must confess I have no clue.
However, if it doesn't, that might be what's causing your headaches.
I'd try moving the SRID filter into a WITH construct or a sub-select
just to see what happens:
WITH filteredgeom AS (
SELECT * FROM gvt WHERE ST_SRID(geometry) = 4326)
SELECT * FROM filteredgeom WHERE within(geometry, ...) ;
Probably lots of syntax errors (haven't had breakfast yet) but you get
the idea...
-- Ture
_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
--
Regards,
Farrukh Najmi
Web: http://www.wellfleetsoftware.com
_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
