[postgis-users] Querying geometries with different srid
Farrukh Najmi
farrukh at wellfleetsoftware.com
Fri Jan 14 08:58:33 PST 2011
Hi Ralf,
I tried the following steps:
1. creating the index: CREATE INDEX GeometryValueType_geom_wgs_idx ON
GeometryValueType using gist (geometry) where ST_Srid(geometry) =
4326;
2. Then ReIndexing the GeometryValueType table
3. Then running your last suggested query but still get "Operation on
mixed SRID geometries" error :-(
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 );
Am I missing something in your instructions? Thanks again for your kind
help.
On 01/14/2011 11:29 AM, Ralf Suhr wrote:
> 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:
>> 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:
>>> 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<farrukh at 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 at postgis.refractions.net
>>>>>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
--
Regards,
Farrukh Najmi
Web: http://www.wellfleetsoftware.com
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20110114/571d0a7e/attachment.html>
More information about the postgis-users
mailing list