[postgis-users] Accessing NWS poly data with lat/lon input

[David Haynes]

Difficult to understand what output you want.

ST_AsText(geom) - will give you the bounding information about your county
polygons.
ST_AsCentroid(geom) - will give you the centroid of the polygon.

What are the lat/long values of?


On Fri, Aug 8, 2014 at 11:24 AM, Nicolas Ribot <nicolas.ribot at gmail.com>
wrote:

> Hello
>
> Considering your geometric column is named geom:
>
> select geom
> from polys
> where st_contains(geom, st_setSRID(st_makePoint(lon, lat), 4326));
>
> (if several polygons contain this point, they will all be returned. Adding
> LIMIT 1 at the end of the query will force to get only one result.
>
> Nicolas
>
>
> On 8 August 2014 17:24, Newcomb, Doug <doug_newcomb at fws.gov> wrote:
>
>> Joseph,
>> I don't see a geometry or geography field in your table listing.
>>
>> Doug
>>
>>
>> On Fri, Aug 8, 2014 at 11:12 AM, Joseph Spenner <joseph85750 at yahoo.com>
>> wrote:
>>
>>> Hello, I've just got postgresql93-server installed, with
>>> postgis21-postgresql93 on a CentOS system.
>>>
>>> I then used shp2pgsql to load in some shape files using this procedure:
>>>
>>>
>>> http://suite.opengeo.org/docs/latest/dataadmin/pgGettingStarted/shp2pgsql.html
>>>
>>> The shapefiles were obtained from NWS:
>>> http://www.nws.noaa.gov/geodata/catalog/wsom/html/pubzone.htm
>>>
>>> Everything appears to have been written to the database properly:
>>>
>>> polydata=# select state,time_zone,zone,name,lon,lat from polys where
>>> state='CO' limit 10;
>>>  state | time_zone | zone |               name               |
>>> lon       |      lat
>>>
>>> -------+-----------+------+----------------------------------+----------------+---------------
>>>  CO    | M         | 048  | Logan County                     |
>>> -103.110114271 | 40.7246902558
>>>  CO    | M         | 044  | Morgan County                    |
>>> -103.809823690 | 40.2627093692
>>>  CO    | M         | 050  | Sedgwick County                  |
>>> -102.351810279 | 40.8758426210
>>>  CO    | M         | 049  | Washington County                |
>>> -103.201287262 | 39.9710250432
>>>  CO    | M         | 090  | Yuma County                      |
>>> -102.424258955 | 40.0029195574
>>>  CO    | M         | 099  | Springfield Vicinity/Baca County |
>>> -102.560453567 | 37.3192132374
>>>  CO    | M         | 097  | Las Animas Vicinity/Bent County  |
>>> -103.071690129 | 37.9551177349
>>>  CO    | M         | 092  | Cheyenne County                  |
>>> -102.603398004 | 38.8279341557
>>>  CO    | M         | 089  | Crowley County                   |
>>> -103.784878922 | 38.3266440954
>>>  CO    | M         | 091  | Kit Carson County                |
>>> -102.602884309 | 39.3054124576
>>> (10 rows)
>>>
>>> polydata=#
>>>
>>> My goal is to construct a query providing lat/lon such that the result
>>> returned is the single row poly containing that point.
>>>
>>> Does anyone know how I would construct this query?
>>>
>>> Thanks!
>>>
>>> Regards,
>>> Joseph Spenner
>>>
>>>
>>> _______________________________________________
>>> postgis-users mailing list
>>> postgis-users at lists.osgeo.org
>>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>>>
>>
>>
>>
>> --
>> Doug Newcomb
>> USFWS
>> Raleigh, NC
>> 919-856-4520 ext. 14 doug_newcomb at fws.gov
>>
>> ---------------------------------------------------------------------------------------------------------
>> 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.
>>
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at lists.osgeo.org
>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20140808/4f855dbe/attachment.html>