[postgis-users] Can't figure out query to find lat/lon (numeric types) in time zone (geometry type). New to GIS.

[Paul Ramsey]

select tz.tzid
from tz_world tz, locations locs
where st_contains(tz.the_geom, st_makepoint(locs.longitude, locs.latitude))
and loc.id = ?;


On Thu, May 31, 2012 at 12:57 PM, Scott Chapman <scott at mischko.com> wrote:
> I have the following table for time zone data:
>
> CREATE TABLE tz_world
> (
>   gid serial NOT NULL,
>   tzid character varying(30),
>   the_geom geometry,
>   CONSTRAINT tz_world_pkey PRIMARY KEY (gid ),
>   CONSTRAINT enforce_dims_the_geom CHECK (st_ndims(the_geom) = 2),
>   CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) =
> 'MULTIPOLYGON'::text OR the_geom IS NULL),
>   CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = (-1))
> )
>
> and a location table:
>
> CREATE TABLE locations
> (
>   id bigserial NOT NULL,
>   latitude numeric,
>   longitude numeric,
> )
>
> Given a location id, I want to know what time zone it's in.  I'm brand new
> to GIS and have not been able to figure out this query. I have PostgreSQL
> 9.1 and PostGIS 1.5.3 installed and set up, and the timezone data imported
> using the shape file import wizard.
>
> Can someone please give me an example of how to query lat/lon in the_geom?
>
> Thanks!
> Scott
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>