[postgis-users] spatial functions in PostGIS connection

[Emily Gouge]

Tom,

The problem resides with the fact that you cannot do geometry operations of geometries with 
different SRIDs.    When creating a geometry from a string as you have outlined below the 'POINT()' 
the SRID is defaulted to -1.  It looks like the geometries in your table are in SRID 4326.

So, try using the setSRID function to change the SRID of the comparison geometry.  Something like:
select oid,the_geom from stations where distance(the_geom,setsrid('POINT(-122 45)'::geometry, 4326)) 
= 10;

As well for better performance try '&&' operator:
select oid,the_geom from stations where the_geom && setsrid ('POINT(-122 45)'::geometry, 4326) and 
distance(the_geom,setsrid('POINT(-122 45)'::geometry, 4326)) = 10;

Emily

Kralidis,Tom [Burlington] wrote:
> Thanks for the info.
> 
> I couldn't get this to work via MapServer, so I tried testing in pgsql:
> 
> devgeodb2=# select oid,the_geom from stations where
> distance(the_geom,'POINT(-122 45)') = 10;
> ERROR:  Operation on two GEOMETRIES with different SRIDs
> 
> devgeodb2=# \d stations;
>                               Table "public.stations"
>      Column     |   Type   |                       Modifiers
> ----------------+----------+--------------------------------------------
> -----------
>  id             | integer  | not null default
> nextval('stations_id_seq'::regclass)
>  station_id     | text     |
>  kyw_id         | text     |
>  title          | text     |
>  abstract       | text     |
>  keywords       | text     |
>  variables      | text     |
>  sensorml_url   | text     |
>  extraction_url | text     |
>  the_geom       | geometry |
>  organization   | text     |
> Indexes:
>     "stations_pkey" PRIMARY KEY, btree (id)
>     "stations_idx" gist (the_geom)
>     "stations_idx_oid" btree (oid)
> Check constraints:
>     "enforce_dims_the_geom" CHECK (ndims(the_geom) = 2)
>     "enforce_geotype_the_geom" CHECK (geometrytype(the_geom) =
> 'POINT'::text OR the_geom IS NULL)
>     "enforce_srid_the_geom" CHECK (srid(the_geom) = 4326)
> 
> 
> ..any idea what I'm doing wrong at the command line level?
> 
> ..Tom
> 
> 
>> -----Original Message-----
>> From: postgis-users-bounces at postgis.refractions.net 
>> [mailto:postgis-users-bounces at postgis.refractions.net] On 
>> Behalf Of Mike Leahy
>> Sent: Tuesday, March 21, 2006 4:28 PM
>> To: PostGIS Users Discussion
>> Subject: Re: [postgis-users] spatial functions in PostGIS connection
>>
>>
>> Tom,
>>
>> I'm pretty sure you can put anything in the data for a 
>> PostGIS layer. At a minimum, I think you need the geometry 
>> plus a unique identifier. Assuming 'gid' is your unique 
>> identifier in the geometry table, you would be able to do 
>> something like:
>>
>> DATA "the_geom from (select gid, the_geom from outbreaks 
>> where distance(wkb_geometry,'POINT(-122 45)') = 10) as foo 
>> using unique gid using srid=-1"
>>
>> So you can put pretty much any query inside the brackets 
>> aliased as 'foo', as long as it contains the geometry column 
>> specified at the start and the unique identifier named at the 
>> end, and that the geometries use the srid also named at the 
>> end of the data string.
>>
>> Mike
>>
>> Kralidis,Tom [Burlington] wrote:
>>> Hi,
>>>
>>> I have a point dataset in PostGIS which I display via 
>> OGC:WMS with no
>>> problems:
>>>
>>>
>>>   NAME "outbreaks"
>>>   CONNECTIONTYPE postgis
>>>   CONNECTION "user=postgres dbname=devgeodb2"
>>>   DATA "the_geom from outbreaks"
>>>
>>> ..is it possible to embed PostGIS functions in the LAYER/DATA 
>>> statement
>>> like:
>>>
>>>   NAME "outbreaks"
>>>   CONNECTIONTYPE postgis
>>>   CONNECTION "user=postgres dbname=devgeodb2"
>>>   DATA "the_geom from outbreaks where 
>>> distance(wkb_geometry,'POINT(-122
>>> 45)') = 10"
>>>
>>> I tried this and got a ServiceException.  I also tried
>>>
>>> Thanks
>>>
>>> ..Tom
>>> _______________________________________________
>>> postgis-users mailing list postgis-users at postgis.refractions.net
>>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>>
>> _______________________________________________
>> postgis-users mailing list postgis-users at postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users