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 <sc...@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@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users > _______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
