[prev in list] [next in list] [prev in thread] [next in thread]
List: postgis-users
Subject: Re: [postgis-users] Can't figure out query to find lat/lon (numeric types) in time zone (geometry ty
From: Scott Chapman <scott () mischko ! com>
Date: 2012-05-31 20:13:59
Message-ID: CA+R6tE+h5CcDB6RxGuUOJg5FO2FHB-pdCNQ_uX=KQ=0Q3+wBvA () mail ! gmail ! com
[Download RAW message or body]
[Attachment #2 (multipart/alternative)]
Thanks Paul!
On Thu, May 31, 2012 at 1:12 PM, Paul Ramsey <pramsey@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@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
>
[Attachment #5 (text/html)]
Thanks Paul!<br><br><div class="gmail_quote">On Thu, May 31, 2012 at 1:12 PM, Paul \
Ramsey <span dir="ltr"><<a href="mailto:pramsey@opengeo.org" \
target="_blank">pramsey@opengeo.org</a>></span> wrote:<br><blockquote \
class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc \
solid;padding-left:1ex"> select tz.tzid<br>
from tz_world tz, locations locs<br>
where st_contains(tz.the_geom, st_makepoint(locs.longitude, locs.latitude))<br>
and <a href="http://loc.id" target="_blank">loc.id</a> = ?;<br>
<div><div class="h5"><br>
<br>
On Thu, May 31, 2012 at 12:57 PM, Scott Chapman <<a \
href="mailto:scott@mischko.com">scott@mischko.com</a>> wrote:<br> > I have the \
following table for time zone data:<br> ><br>
> CREATE TABLE tz_world<br>
> (<br>
> gid serial NOT NULL,<br>
> tzid character varying(30),<br>
> the_geom geometry,<br>
> CONSTRAINT tz_world_pkey PRIMARY KEY (gid ),<br>
> CONSTRAINT enforce_dims_the_geom CHECK (st_ndims(the_geom) = 2),<br>
> CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) =<br>
> 'MULTIPOLYGON'::text OR the_geom IS NULL),<br>
> CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = (-1))<br>
> )<br>
><br>
> and a location table:<br>
><br>
> CREATE TABLE locations<br>
> (<br>
> id bigserial NOT NULL,<br>
> latitude numeric,<br>
> longitude numeric,<br>
> )<br>
><br>
> Given a location id, I want to know what time zone it's in. I'm brand \
new<br> > to GIS and have not been able to figure out this query. I have \
PostgreSQL<br> > 9.1 and PostGIS 1.5.3 installed and set up, and the timezone data \
imported<br> > using the shape file import wizard.<br>
><br>
> Can someone please give me an example of how to query lat/lon in the_geom?<br>
><br>
> Thanks!<br>
> Scott<br>
><br>
</div></div>> _______________________________________________<br>
> postgis-users mailing list<br>
> <a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>
> <a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" \
target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br> \
><br> _______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>
<a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" \
target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br> \
</blockquote></div><br>
_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic