[postgis-users] Querying geometries with different srid

[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, 
'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<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