[postgis-users] Can't figure out query to find lat/lon (numeric types) in time zone (geometry type). New to GIS.
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
Re: [postgis-users] Can't figure out query to find lat/lon (numeric types) in time zone (geometry type). New to GIS.
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
Re: [postgis-users] Can't figure out query to find lat/lon (numeric types) in time zone (geometry type). New to GIS.
Thanks Paul! On Thu, May 31, 2012 at 1:12 PM, Paul Ramsey pram...@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 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 ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
