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

[Nicolas Ribot]

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
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20140808/e23fa9ee/attachment.html>