<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html; charset=UTF-8" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Hi Ralf,<br>
<br>
I tried the following steps:<br>
<br>
<ol>
<li>creating the index: CREATE INDEX
GeometryValueType_geom_wgs_idx ON GeometryValueType using gist
(geometry) where ST_Srid(geometry) = 4326;</li>
<li>Then ReIndexing the GeometryValueType table</li>
<li>Then running your last suggested query but still get
"Operation on mixed SRID geometries" error :-(<br>
<pre wrap="">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 );</pre>
</li>
</ol>
Am I missing something in your instructions? Thanks again for your
kind help.<br>
<br>
<br>
<br>
On 01/14/2011 11:29 AM, Ralf Suhr wrote:
<blockquote cite="mid:201101141729.04376.Ralf.Suhr@itc-halle.de"
type="cite">
<pre wrap="">Hi Farrukh,
your setup produce this error.
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
);
or
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;
become the same in postgresql planner. I've testet all querys with Postgis
1.4.0 without any error. Except
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 );
But you can create a partial index and the last query will work:
CREATE INDEX GeometryValueType_geom_wgs_idx ON GeometryValueType using gist
(geometry) where ST_Srid(geometry) = 4326;
Gr
Ralf
Am Freitag 14 Januar 2011, 16:33:33 schrieb Farrukh Najmi:
</pre>
<blockquote type="cite">
<pre wrap="">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,
'0103000020E6100000010000000500000000000000000059C0000000000000000000000000
0000000000000000000000000000000000000000000000000000594000000000000059C0000
000000000594000000000000059C00000000000000000'::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:
</pre>
<blockquote type="cite">
<pre wrap="">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:
</pre>
<blockquote type="cite">
<pre wrap="">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:
</pre>
<blockquote type="cite">
<pre wrap="">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:
</pre>
<blockquote type="cite">
<pre wrap="">2011/1/14 Farrukh Najmi<a class="moz-txt-link-rfc2396E" href="mailto:farrukh@wellfleetsoftware.com"><farrukh@wellfleetsoftware.com></a>:
</pre>
<blockquote type="cite">
<blockquote type="cite">
<pre wrap="">--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 )
</pre>
</blockquote>
</blockquote>
<pre wrap="">
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
<a class="moz-txt-link-abbreviated" href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a>
<a class="moz-txt-link-freetext" href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</a>
</pre>
</blockquote>
</blockquote>
</blockquote>
</blockquote>
</blockquote>
<pre wrap="">
</pre>
</blockquote>
<br>
<br>
<pre class="moz-signature" cols="72">--
Regards,
Farrukh Najmi
Web: <a class="moz-txt-link-freetext" href="http://www.wellfleetsoftware.com">http://www.wellfleetsoftware.com</a>
</pre>
</body>
</html>