[postgis-users] Accessing NWS poly data with lat/lon input
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@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Re: [postgis-users] Accessing NWS poly data with lat/lon input
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 joseph85...@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@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users -- Doug Newcomb USFWS Raleigh, NC 919-856-4520 ext. 14 doug_newc...@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@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Re: [postgis-users] Accessing NWS poly data with lat/lon input
Joseph,
Short answer - ST_Intersects, see
http://gis.stackexchange.com/questions/75537/how-to-run-st-intersects-on-a-geometry-from-text-and-an-existing-table
*see
alsohttp://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
*
Longer answer - go through the tutorial at
http://workshops.boundlessgeo.com/postgis-intro/ :-)
There are a couple of postgis - specific books that are available as well.
Doug
On Fri, Aug 8, 2014 at 11:35 AM, Joseph Spenner joseph85...@yahoo.com
wrote:
Doug:
Thanks for the reply! Sorry, I'm a bit new at this and kinda diving in
head first.
Does this help:
polydata-# \d polys
Table public.polys
Column |Type |
Modifiers
+-+-
gid| integer | not null default
nextval('polys_gid_seq'::regclass)
state | character varying(2)|
cwa| character varying(9)|
time_zone | character varying(2)|
fe_area| character varying(2)|
zone | character varying(3)|
name | character varying(254) |
state_zone | character varying(5)|
lon| numeric |
lat| numeric |
shortname | character varying(32) |
geom | geometry(MultiPolygon,4269) |
Indexes:
polys_pkey PRIMARY KEY, btree (gid)
polys_geom_gist gist (geom)
polydata-#
---
?
If life gives you lemons, keep them-- because hey.. free lemons.
~heart~ Sticker fixer:
http://microflush.org/stuff/stickers/heartFix.html
--
*From:* Newcomb, Doug doug_newc...@fws.gov
*To:* Joseph Spenner joseph85...@yahoo.com; PostGIS Users Discussion
postgis-users@lists.osgeo.org
*Sent:* Friday, August 8, 2014 8:24 AM
*Subject:* Re: [postgis-users] Accessing NWS poly data with lat/lon input
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 joseph85...@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@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
--
Doug Newcomb
USFWS
Raleigh, NC
919-856-4520 ext. 14 doug_newc...@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@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
--
Re: [postgis-users] Accessing NWS poly data with lat/lon input
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_newc...@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 joseph85...@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@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users -- Doug Newcomb USFWS Raleigh, NC 919-856-4520 ext. 14 doug_newc...@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@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Re: [postgis-users] Accessing NWS poly data with lat/lon input
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.ri...@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_newc...@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 joseph85...@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@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users -- Doug Newcomb USFWS Raleigh, NC 919-856-4520 ext. 14 doug_newc...@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@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Re: [postgis-users] Accessing NWS poly data with lat/lon input
Doug:
That was it!
polydata=# select zone,gid,state,name,ST_AsKML( geom ) from polys where
ST_Intersects(ST_PointFromText('POINT(-109 42)', 4269), geom);
Thanks for the help and links!
Regards,
Joseph Spenner
From: Newcomb, Doug doug_newc...@fws.gov
To: Joseph Spenner joseph85...@yahoo.com; PostGIS Users Discussion
postgis-users@lists.osgeo.org
Sent: Friday, August 8, 2014 9:13 AM
Subject: Re: [postgis-users] Accessing NWS poly data with lat/lon input
Joseph,
Short answer - ST_Intersects, see
http://gis.stackexchange.com/questions/75537/how-to-run-st-intersects-on-a-geometry-from-text-and-an-existing-table
see also
http://gis.stackexchange.com/questions/51869/how-to-find-out-whether-a-lat-long-position-resides-within-a-polygon
Longer answer - go through the tutorial at
http://workshops.boundlessgeo.com/postgis-intro/ :-)
There are a couple of postgis - specific books that are available as well.
Doug
On Fri, Aug 8, 2014 at 11:35 AM, Joseph Spenner joseph85...@yahoo.com wrote:
Doug:
Thanks for the reply! Sorry, I'm a bit new at this and kinda diving in
head first.
Does this help:
polydata-# \d polys
Table public.polys
Column | Type | Modifiers
+-+-
gid | integer | not null default
nextval('polys_gid_seq'::regclass)
state | character varying(2) |
cwa | character varying(9) |
time_zone | character varying(2) |
fe_area | character varying(2) |
zone | character varying(3) |
name | character varying(254) |
state_zone | character varying(5) |
lon | numeric |
lat | numeric |
shortname | character varying(32) |
geom | geometry(MultiPolygon,4269) |
Indexes:
polys_pkey PRIMARY KEY, btree (gid)
polys_geom_gist gist (geom)
polydata-#
---
?
If life gives you lemons, keep them-- because hey.. free lemons.
~heart~ Sticker fixer: http://microflush.org/stuff/stickers/heartFix.html
From: Newcomb, Doug doug_newc...@fws.gov
To: Joseph Spenner joseph85...@yahoo.com; PostGIS Users Discussion
postgis-users@lists.osgeo.org
Sent: Friday, August 8, 2014 8:24 AM
Subject: Re: [postgis-users] Accessing NWS poly data with lat/lon input
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 joseph85...@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@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
--
Doug Newcomb
USFWS
Raleigh, NC
919-856-4520 ext. 14 doug_newc...@fws.gov
[postgis-users] PostGIS 2.1 and PostgreSQL 9.3 on Ubuntu 14.0.4 (Trusty) with PostgreSQL apt Repo
I know some people were complaining on the list about being able to install PostGIS on Ubuntu trusty. I finally spun up a Trusty VM 64-bit and was able to successfully install using Apt PostgreSQL repo. I've updated the wiki instructions: http://trac.osgeo.org/postgis/wiki/UsersWikiPostGIS21UbuntuPGSQL93Apt Hopefully this helps others. Let me know if the updates don't work for you. Thanks, Regina http://www.postgis.us http://postgis.net ___ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
