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

[Scott Chapman]

Thanks Paul!

On Thu, May 31, 2012 at 1:12 PM, Paul Ramsey <pramsey at opengeo.org> wrote:

> 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
> >
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20120531/11026caf/attachment.html>