<html><body><div style="color:#000; background-color:#fff; font-family:Courier New, courier, monaco, monospace, sans-serif;font-size:14pt"><span class="" style="font-family: HelveticaNeue,Helvetica Neue,Helvetica,Arial,Lucida Grande,sans-serif;"><span class="" style="font-family: times new roman,new york,times,serif;">Doug:<br style=""> That was it!<br style="" class=""><br style="" class="">polydata=# select zone,gid,state,name,ST_AsKML( geom ) from polys where ST_Intersects(ST_PointFromText('POINT(-109 42)', 4269), geom);<br><br>Thanks for the help and links!<br><br>Regards,<br>Joseph Spenner<br style="" class=""><br style="" class=""></span></span><div class="" style="font-family: Courier New, courier, monaco, monospace, sans-serif; font-size: 14pt;"> <div class="" style="font-family: HelveticaNeue, Helvetica Neue, Helvetica, Arial, Lucida Grande, sans-serif; font-size: 12pt;"> <div style="" class="" dir="ltr"> <hr style="" class="" size="1">
<font style="" class="" face="Arial" size="2"> <b style="" class=""><span class="" style="font-weight:bold;">From:</span></b> "Newcomb, Doug" <doug_newcomb@fws.gov><br style="" class=""> <b style="" class=""><span class="" style="font-weight: bold;">To:</span></b> Joseph Spenner <joseph85750@yahoo.com>; PostGIS Users Discussion <postgis-users@lists.osgeo.org> <br style="" class=""> <b style="" class=""><span class="" style="font-weight: bold;">Sent:</span></b> Friday, August 8, 2014 9:13 AM<br style="" class=""> <b style="" class=""><span class="" style="font-weight: bold;">Subject:</span></b> Re: [postgis-users] Accessing NWS poly data with lat/lon input<br style="" class=""> </font> </div> <div style="" class=""><br style="" class=""><div style="" class="" id="yiv8120675952"><div style="" class=""><div style="" class="" dir="ltr">Joseph,<div style="" class=""><br style="" class="" clear="none"></div><div style="" class="">Short
answer - <span style="" class="">ST_Intersects, see </span><a style="" class="" rel="nofollow" shape="rect" target="_blank" href="http://gis.stackexchange.com/questions/75537/how-to-run-st-intersects-on-a-geometry-from-text-and-an-existing-table">http://gis.stackexchange.com/questions/75537/how-to-run-st-intersects-on-a-geometry-from-text-and-an-existing-table</a></div>
<div style="" class=""><b style="" class=""></b><div class="" style="font-weight:normal;"><b style="" class=""><br style="" class="" clear="none"></b></div><b style="" class=""></b><div class="" style="font-weight:normal;"><b style="" class="">see also</b></div><b style="" class=""></b><div class="" style="font-weight:normal;"><b style="" class=""><span style="" class=""><a style="" class="" rel="nofollow" shape="rect" target="_blank" href="http://gis.stackexchange.com/questions/51869/how-to-find-out-whether-a-lat-long-position-resides-within-a-polygon">http://gis.stackexchange.com/questions/51869/how-to-find-out-whether-a-lat-long-position-resides-within-a-polygon</a></span></b></div><b style="" class="">
</b><div class="" style="font-weight:normal;"><b style="" class=""><span style="" class=""><br style="" class="" clear="none"></span></b></div><b style="" class=""></b></div><div style="" class=""><span style="" class="">Longer answer - go through the tutorial at <a style="" class="" rel="nofollow" shape="rect" target="_blank" href="http://workshops.boundlessgeo.com/postgis-intro/">http://workshops.boundlessgeo.com/postgis-intro/</a> :-)</span></div>
<div style="" class="">There are a couple of postgis - specific books that are available as well.</div><div style="" class=""><span style="" class=""><br style="" class="" clear="none"></span></div><div style="" class="">Doug</div><div style="" class=""><br style="" class="" clear="none"></div></div><div style="" class=""><br style="" class="" clear="none"><br style="" class="" clear="none"><div style="" class="">On Fri, Aug 8, 2014 at 11:35 AM, Joseph Spenner <span style="" class="" dir="ltr"><<a style="" class="" rel="nofollow" shape="rect" ymailto="mailto:joseph85750@yahoo.com" target="_blank" href="mailto:joseph85750@yahoo.com">joseph85750@yahoo.com</a>></span> wrote:<br style="" class="" clear="none">
<blockquote class="" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;"><div style="" class="" id="yiv8120675952yqt65765"><div style="" class=""><div class="" style="color:#000;background-color:#fff;font-family:garamond, new york, times, serif;font-size:14pt;"><div style="" class=""><span style="" class="">Doug:</span></div>
<div class="" style="color:rgb(0,0,0);font-size:18.6667px;font-family:garamond, new york, times, serif;background-color:transparent;font-style:normal;"><span style="" class=""> Thanks for the reply! Sorry, I'm a bit new at this and kinda diving in head first.</span></div>
<div class="" style="color:rgb(0,0,0);font-size:18.6667px;font-family:garamond, new york, times, serif;background-color:transparent;font-style:normal;"><br style="" class="" clear="none"><span style="" class=""></span></div><div class="" style="color:rgb(0,0,0);font-size:18.6667px;font-family:garamond, new york, times, serif;background-color:transparent;font-style:normal;">
<span style="" class="">Does this help:</span></div><div class="" style="color:rgb(0,0,0);font-size:18.6667px;font-family:garamond, new york, times, serif;background-color:transparent;font-style:normal;"><br style="" class="" clear="none"><span style="" class=""></span></div><div class="" style="color:rgb(0,0,0);font-size:18.6667px;font-family:garamond, new york, times, serif;background-color:transparent;font-style:normal;">
<span style="" class="">----</span></div><div class="" style="color:rgb(0,0,0);font-size:18.6667px;font-family:garamond, new york, times, serif;background-color:transparent;font-style:normal;"><br style="" class="" clear="none"></div><div style="" class=""><span style="" class=""><span class="" style="font-family:Courier New, courier, monaco, monospace, sans-serif;">polydata-# \d polys<br style="" class="" clear="none">
Table "public.polys"<br style="" class="" clear="none"> Column | Type | Modifiers <br style="" class="" clear="none">------------+-----------------------------+-----------------------------------------------------<br style="" class="" clear="none">
gid | integer | not null default nextval('polys_gid_seq'::regclass)<br style="" class="" clear="none"> state | character varying(2) |
<br style="" class="" clear="none"> cwa | character varying(9) | <br style="" class="" clear="none"> time_zone | character varying(2) | <br style="" class="" clear="none"> fe_area | character varying(2) | <br style="" class="" clear="none"> zone | character varying(3) | <br style="" class="" clear="none"> name | character varying(254) | <br style="" class="" clear="none">
state_zone | character varying(5) | <br style="" class="" clear="none"> lon | numeric | <br style="" class="" clear="none"> lat | numeric | <br style="" class="" clear="none"> shortname | character varying(32) | <br style="" class="" clear="none"> geom | geometry(MultiPolygon,4269) | <br style="" class="" clear="none">
Indexes:<br style="" class="" clear="none"> "polys_pkey" PRIMARY KEY, btree (gid)<br style="" class="" clear="none"> "polys_geom_gist" gist (geom)<br style="" class="" clear="none"><br style="" class="" clear="none">polydata-# </span><br style="" class="" clear="none"></span></div><div class="" style="color:rgb(0,0,0);font-size:18.6667px;font-family:garamond, new york, times, serif;background-color:transparent;font-style:normal;">
<br style="" class="" clear="none"><span style="" class=""></span></div><div class="" style="color:rgb(0,0,0);font-size:18.6667px;font-family:garamond, new york, times, serif;background-color:transparent;font-style:normal;"><span style="" class="">---</span></div><div class="" style="color:rgb(0,0,0);font-size:18.6667px;font-family:garamond, new york, times, serif;background-color:transparent;font-style:normal;">
<span style="" class="">?</span></div><div class="" style="color:rgb(0,0,0);font-size:18.6667px;font-family:garamond, new york, times, serif;background-color:transparent;font-style:normal;"><span style="" class=""><br style="" class="" clear="none"></span></div><div style="" class=""> </div><div style="" class="">If life gives you lemons, keep them-- because hey.. free lemons.
</div><div style="" class=""><br style="" class="" clear="none"><br style="" class="" clear="none"></div><div style="" class="">
<br style="" class="" clear="none">"~heart~ Sticker" fixer: <a style="" class="" rel="nofollow" shape="rect" target="_blank" href="http://microflush.org/stuff/stickers/heartFix.html">http://microflush.org/stuff/stickers/heartFix.html</a>
</div><div style="" class=""><br style="" class="" clear="none"></div><div style="" class=""><div style="" class=""><br style="" class="" clear="none"></div><div style="" class="">
<br style="" class="" clear="none"></div><div style="" class=""><br style="" class="" clear="none"></div> </div><div class="" style="font-family:garamond, new york, times, serif;font-size:14pt;"><div style="" class=""> </div><div class="" style="font-family:HelveticaNeue, Helvetica Neue, Helvetica, Arial, Lucida Grande, sans-serif;font-size:12pt;">
<div style="" class=""> <div style="" class="" dir="ltr"> <hr style="" class="" size="1"> <font style="" class="" face="Arial"> <b style="" class=""><span class="" style="font-weight:bold;">From:</span></b> "Newcomb, Doug" <<a style="" class="" rel="nofollow" shape="rect" ymailto="mailto:doug_newcomb@fws.gov" target="_blank" href="mailto:doug_newcomb@fws.gov">doug_newcomb@fws.gov</a>><br style="" class="" clear="none">
<b style="" class=""><span class="" style="font-weight:bold;">To:</span></b> Joseph Spenner <<a style="" class="" rel="nofollow" shape="rect" ymailto="mailto:joseph85750@yahoo.com" target="_blank" href="mailto:joseph85750@yahoo.com">joseph85750@yahoo.com</a>>; PostGIS Users Discussion <<a style="" class="" rel="nofollow" shape="rect" ymailto="mailto:postgis-users@lists.osgeo.org" target="_blank" href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a>> <br style="" class="" clear="none">
<b style="" class=""><span class="" style="font-weight:bold;">Sent:</span></b> Friday, August 8, 2014 8:24 AM<br style="" class="" clear="none"> <b style="" class=""><span class="" style="font-weight:bold;">Subject:</span></b> Re: [postgis-users] Accessing NWS poly data with lat/lon input<br style="" class="" clear="none"> </font> </div>
</div><div style="" class=""><div style="" class=""> <div style="" class=""><br style="" class="" clear="none"><div style="" class=""><div style="" class=""><div style="" class="" dir="ltr">Joseph,<div style="" class="">I don't see a geometry or geography field in your table listing.</div><div style="" class=""><br style="" class="" clear="none"></div><div style="" class="">Doug </div></div><div style="" class=""><br style="" class="" clear="none">
<br style="" class="" clear="none">
<div style="" class="">On Fri, Aug 8, 2014 at 11:12 AM, Joseph Spenner <span style="" class="" dir="ltr"><<a style="" class="" rel="nofollow" shape="rect" ymailto="mailto:joseph85750@yahoo.com" target="_blank" href="mailto:joseph85750@yahoo.com">joseph85750@yahoo.com</a>></span> wrote:<br style="" class="" clear="none">
<blockquote class="" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;"><div style="" class=""><div style="" class=""><div class="" style="color:#000;background-color:#fff;font-family:garamond, new york, times, serif;font-size:14pt;">Hello, I've just got postgresql93-server installed, with postgis21-postgresql93 on a CentOS system.<br style="" class="" clear="none">
<br style="" class="" clear="none">I then used shp2pgsql to load in some shape files using this procedure:<br style="" class="" clear="none"><br style="" class="" clear="none"><a style="" class="" rel="nofollow" shape="rect" target="_blank" href="http://suite.opengeo.org/docs/latest/dataadmin/pgGettingStarted/shp2pgsql.html">http://suite.opengeo.org/docs/latest/dataadmin/pgGettingStarted/shp2pgsql.html</a><br style="" class="" clear="none">
<br style="" class="" clear="none">The shapefiles were obtained from NWS:<br style="" class="" clear="none"><a style="" class="" rel="nofollow" shape="rect" target="_blank" href="http://www.nws.noaa.gov/geodata/catalog/wsom/html/pubzone.htm">http://www.nws.noaa.gov/geodata/catalog/wsom/html/pubzone.htm</a><br style="" class="" clear="none">
<br style="" class="" clear="none">Everything appears to have been written to the database properly:<br style="" class="" clear="none">
<br style="" class="" clear="none">polydata=# select state,time_zone,zone,name,lon,lat from polys where state='CO' limit 10;<br style="" class="" clear="none"> state | time_zone | zone | name | lon | lat <br style="" class="" clear="none">
-------+-----------+------+----------------------------------+----------------+---------------<br style="" class="" clear="none">
CO | M | 048 | Logan County | -103.110114271 | 40.7246902558<br style="" class="" clear="none"> CO | M |
044 | Morgan County | -103.809823690 | 40.2627093692<br style="" class="" clear="none"> CO | M | 050 | Sedgwick County | -102.351810279 | 40.8758426210<br style="" class="" clear="none"> CO | M | 049 | Washington County | -103.201287262 | 39.9710250432<br style="" class="" clear="none">
CO | M | 090 | Yuma County | -102.424258955 |
40.0029195574<br style="" class="" clear="none"> CO | M | 099 | Springfield Vicinity/Baca County | -102.560453567 | 37.3192132374<br style="" class="" clear="none"> CO | M | 097 | Las Animas Vicinity/Bent County | -103.071690129 | 37.9551177349<br style="" class="" clear="none">
CO | M | 092 | Cheyenne County | -102.603398004 | 38.8279341557<br style="" class="" clear="none">
CO | M | 089 | Crowley County | -103.784878922 | 38.3266440954<br style="" class="" clear="none"> CO |
M | 091 | Kit Carson County | -102.602884309 | 39.3054124576<br style="" class="" clear="none">(10 rows)<br style="" class="" clear="none"><br style="" class="" clear="none">polydata=#<br style="" class="" clear="none"><br style="" class="" clear="none">My goal is to construct a query providing lat/lon such that the result returned is the single row poly containing that point.<br style="" class="" clear="none">
<br style="" class="" clear="none">Does anyone know how I would construct this query?<br style="" class="" clear="none"><div style="" class="">
<br style="" class="" clear="none">Thanks!<br style="" class="" clear="none"><br style="" class="" clear="none">Regards,<br style="" class="" clear="none">Joseph Spenner<br style="" class="" clear="none"><br style="" class="" clear="none"></div></div></div></div><br style="" class="" clear="none">_______________________________________________<br style="" class="" clear="none">
postgis-users mailing list<br style="" class="" clear="none">
<a style="" class="" rel="nofollow" shape="rect" ymailto="mailto:postgis-users@lists.osgeo.org" target="_blank" href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br style="" class="" clear="none">
<a style="" class="" rel="nofollow" shape="rect" target="_blank" href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br style="" class="" clear="none"></blockquote></div><br style="" class="" clear="none">
<br style="" class="" clear="all"><div style="" class=""><br style="" class="" clear="none"></div>-- <br style="" class="" clear="none"><div style="" class="">
Doug Newcomb</div><div style="" class="">USFWS</div><div style="" class="">Raleigh, NC</div><div style="" class="">919-856-4520 ext. 14 <a style="" class="" rel="nofollow" shape="rect" ymailto="mailto:doug_newcomb@fws.gov" target="_blank" href="mailto:doug_newcomb@fws.gov">doug_newcomb@fws.gov</a></div><div style="" class="">---------------------------------------------------------------------------------------------------------</div>
<div style="" class="">The opinions I express are my own and are not representative of the official policy of the U.S.Fish and Wildlife Service or Dept. of the Interior. Life is too short for undocumented, proprietary data formats.</div>
</div></div></div><br style="" class="" clear="none"><br style="" class="" clear="none"></div> </div></div></div> </div> </div></div></div><br style="" class="" clear="none">_______________________________________________<br style="" class="" clear="none">
postgis-users mailing list<br style="" class="" clear="none">
<a style="" class="" rel="nofollow" shape="rect" ymailto="mailto:postgis-users@lists.osgeo.org" target="_blank" href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br style="" class="" clear="none">
<a style="" class="" rel="nofollow" shape="rect" target="_blank" href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br style="" class="" clear="none"></blockquote></div><br style="" class="" clear="none"><br style="" class="" clear="all"><div style="" class=""><br style="" class="" clear="none"></div>-- <br style="" class="" clear="none"><div style="" class="">
Doug Newcomb</div><div style="" class="">USFWS</div><div style="" class="">Raleigh, NC</div><div style="" class="">919-856-4520 ext. 14 <a style="" class="" rel="nofollow" shape="rect" ymailto="mailto:doug_newcomb@fws.gov" target="_blank" href="mailto:doug_newcomb@fws.gov">doug_newcomb@fws.gov</a></div><div style="" class="">---------------------------------------------------------------------------------------------------------</div>
<div style="" class="">The opinions I express are my own and are not representative of the official policy of the U.S.Fish and Wildlife Service or Dept. of the Interior. Life is too short for undocumented, proprietary data formats.</div>
</div></div></div><br style="" class=""><br style="" class=""></div> </div> </div> </div></body></html>