Re: [postgis-users] Finding Islands
I figure you have spatially indexed the polygons already? Any way of pre-categorising your polygons - binning them in some way that allows a non spatial test in the where clause to replace the spatial test... eg: a boolean attribute set to indicate if a feature has any part = a particular x value or not. run this against the 2m features once to populate it, and assuming you get a 50/50 split of T/F features, your 2m^2 query can instead include a where a.bool = b.bool, as we already know that if the boolean flag is different, they cannot touch, so your query should involve a spatial test only over 1m^2 instead... if you do this on both x y, the boolean filter will replace even more spatial calculations drop them to 500,000^2 tests... If you can pre-classify features so that non-spatial tests can reduce the spatial ones (esp for features with lots of vertices) in a where clause, such queries do run much faster, but you have the trade-off of the time taken to carry out the classification... which is only n*no_classes/2, generally much faster than n^n Hope this makes sense... Brent Wood From: Lee Hachadoorian lee.hachadooria...@gmail.com To: PostGIS Users postgis-us...@postgis.refractions.net Sent: Wednesday, November 20, 2013 8:52 PM Subject: [postgis-users] Finding Islands I am trying to find islands, polygons in a (multi)polygon layer which are not connected to any other polygons in the same layer. What I came up with runs in a couple of seconds on a layer with ~1000 geometries, and a couple of minutes on a layer with ~23,000 geometries, but I want to run it on a layer of 2 million+ and it's taking a L-O-O-O-NG time, presumably because it's making (2 million)^2 comparisons. What I came up with is: SELECT a.* FROM table a LEFT JOIN table b ON (ST_Touches(a.geom, b.geom)) WHERE b.gid is null or SELECT * FROM table WHERE gid NOT IN ( SELECT DISTINCT a.gid FROM table a JOIN table b ON (ST_Intersects(a.geom, b.geom) AND a.gid != b.gid) ) The first variant raises NOTICE: geometry_gist_joinsel called with incorrect join type. So I thought I could improve performance with an INNER JOIN instead of an OUTER JOIN, and came up with the second variant, and it does seem to perform somewhat better. Any suggestions for how to speed up either approach? Best, --Lee -- Lee Hachadoorian Assistant Professor in Geography, Dartmouth College http://freecity.commons.gc.cuny.edu ___ 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] Finding Islands
Maybe you can try the stupidest way to go, anyway you have to do a inner product because you have to consider each pair of polygons. CREATE TABLE result AS SELECT DISTINCT ON (a.gid) a.* FROM table AS a , table AS b WHERE ST_Intersects(a.geom, b.geom) = TRUE AND a.gid != b.gid Now you can improve, because in the previous query you will compute (geom1,geom2) and (geom2,geom1), which is duplicate so you can try creating an index on gid , and CREATE TABLE result2 AS WITH direct_comp AS (SELECT a.gid AS agid, a.geom AS ageom , b.gid AS bgid, b.geom AS bgeom FROM table AS a , table AS b WHERE ST_Intersects(a.geom, b.geom) = TRUE AND a.gid b.gid) SELECT agid AS gid, ageom AS geom FROM direct_comp UNION SELECT bgid AS gid, bgeom AS geom FROM direct_comp Now if you want to go really big, this will be difficult. Assuming your polygons bboxes have a homogenous size and/or are not too big reguarding the total area . The idea is to group polygons into same cells, so when you try to find which polygons intersects, instead of comparing a polygon to each other polygon, you compare a polygon to each other polygon in the same cell. create a grid of cells (big enough or you will have lot's of cells) index the table for each cell, get polygons intersecting it. This defines groups of polygons in the same cell, defined by a new id group_id index group_id Now you will have several options depending on your hardware/data 1) process a cell at a time if limited hardware, adding WHERE group_id=XX 2) do a massiv inner join on the group_id and compute all In fact you could avoid to create explicitly the cells, but it will be more complicated. Cheers, Rémi-C 2013/11/20 Brent Wood pcr...@pcreso.com I figure you have spatially indexed the polygons already? Any way of pre-categorising your polygons - binning them in some way that allows a non spatial test in the where clause to replace the spatial test... eg: a boolean attribute set to indicate if a feature has any part = a particular x value or not. run this against the 2m features once to populate it, and assuming you get a 50/50 split of T/F features, your 2m^2 query can instead include a where a.bool = b.bool, as we already know that if the boolean flag is different, they cannot touch, so your query should involve a spatial test only over 1m^2 instead... if you do this on both x y, the boolean filter will replace even more spatial calculations drop them to 500,000^2 tests... If you can pre-classify features so that non-spatial tests can reduce the spatial ones (esp for features with lots of vertices) in a where clause, such queries do run much faster, but you have the trade-off of the time taken to carry out the classification... which is only n*no_classes/2, generally much faster than n^n Hope this makes sense... Brent Wood -- *From:* Lee Hachadoorian lee.hachadooria...@gmail.com *To:* PostGIS Users postgis-us...@postgis.refractions.net *Sent:* Wednesday, November 20, 2013 8:52 PM *Subject:* [postgis-users] Finding Islands I am trying to find islands, polygons in a (multi)polygon layer which are not connected to any other polygons in the same layer. What I came up with runs in a couple of seconds on a layer with ~1000 geometries, and a couple of minutes on a layer with ~23,000 geometries, but I want to run it on a layer of 2 million+ and it's taking a L-O-O-O-NG time, presumably because it's making (2 million)^2 comparisons. What I came up with is: SELECT a.* FROM table a LEFT JOIN table b ON (ST_Touches(a.geom, b.geom)) WHERE b.gid is null or SELECT * FROM table WHERE gid NOT IN ( SELECT DISTINCT a.gid FROM table a JOIN table b ON (ST_Intersects(a.geom, b.geom) AND a.gid != b.gid) ) The first variant raises NOTICE: geometry_gist_joinsel called with incorrect join type. So I thought I could improve performance with an INNER JOIN instead of an OUTER JOIN, and came up with the second variant, and it does seem to perform somewhat better. Any suggestions for how to speed up either approach? Best, --Lee -- Lee Hachadoorian Assistant Professor in Geography, Dartmouth College http://freecity.commons.gc.cuny.edu ___ 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
[postgis-users] Difference on check constraints on colum type between Pgis 1.5 and 2.0 ?
Hye.
I have a question about the check constraints on the PostGIS columns type
Can you explain me why we got check constraints on old version (here 1.5.2) of
PostGIS and now we don't see such constraints with 2.0 when we ask a describe
of such relation.
I precise that the 2 objects have been created with same query (same function)
on two servers.
select AddGeometryColumn('d1','contour',4326,'MULTIPOLYGON',2);
POSTGIS 1.5
___
bd= \d d1
Column | Type | Modifiers
--+--+---
idmenage | integer | not null
contour | geometry |
Indexes:
idx1_d1 btree (idmenage), tablespace bde_data
Check constraints:
enforce_dims_contour CHECK (st_ndims(contour) = 2)
enforce_geotype_contour CHECK (geometrytype(contour) = 'MULTIPOLYGON'::text
OR contour IS NULL)
enforce_srid_contour CHECK (st_srid(contour) = 4326)
POSTGIS 2.0
___
bde= \d d1
Column | Type | Modifiers
--+-+---
idmenage | integer | not null
contour | geometry(MultiPolygon,4326) |
Indexes:
idx1_d1 btree (idmenage)
The difference is that the type added by function is not really the same.
Are constraints not visible but internal ?
Thanks.
David PEYRIERES
Direction des Sytèmes d'Information
Division Etudes et Développements
Administration et Support Bases de Données
Tel : +33 (0)5.61.07.83.36
Fax : +33 (0)5.61.07.81.09
david.peyrie...@meteo.fr
METEO FRANCE
www.meteo.fr
42 avenue Gustave Coriolis
31057 TOULOUSE Cédex
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Re: [postgis-users] The first release of the PostGIS Add-ons is out!
I would have gone with a license saying You can modify and redistribute as long as the derived work is also under an open license, not necessarily GPL. Does that make sense? Does that exist? http://en.wikipedia.org/wiki/WTFPL Best regards, -- Mateusz Loskot, http://mateusz.loskot.net ___ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Re: [postgis-users] The first release of the PostGIS Add-ons is out!
Not really the same thing as I said... ;-) I would prefer You just DO WHAT THE FUCK YOU WANT TO as long as it stay open... -Original Message- From: postgis-users-boun...@lists.osgeo.org [mailto:postgis-users- boun...@lists.osgeo.org] On Behalf Of Mateusz Loskot Sent: Wednesday, November 20, 2013 9:52 AM To: PostGIS Users Discussion Subject: Re: [postgis-users] The first release of the PostGIS Add-ons is out! I would have gone with a license saying You can modify and redistribute as long as the derived work is also under an open license, not necessarily GPL. Does that make sense? Does that exist? http://en.wikipedia.org/wiki/WTFPL Best regards, -- Mateusz Loskot, http://mateusz.loskot.net ___ 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] The first release of the PostGIS Add-ons is out!
On Wed, Nov 20, 2013 at 09:56:56AM -0500, Pierre Racine wrote: Not really the same thing as I said... ;-) I would prefer You just DO WHAT THE FUCK YOU WANT TO as long as it stay open... That's GPL, really. The only reason for people NOT to like it is when they want to craft some piece of closed source and still use what you send out to the world as an open component. --strk; ___ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Re: [postgis-users] The first release of the PostGIS Add-ons is out!
There is a very big difference between :' i tweak one of your function, so my tweak must be open source' and 'i use one of your function, so my code must be open source'. Unfortunately GPL imposes both. Cheers, Rémi-C 2013/11/20 Sandro Santilli s...@keybit.net On Wed, Nov 20, 2013 at 09:56:56AM -0500, Pierre Racine wrote: Not really the same thing as I said... ;-) I would prefer You just DO WHAT THE FUCK YOU WANT TO as long as it stay open... That's GPL, really. The only reason for people NOT to like it is when they want to craft some piece of closed source and still use what you send out to the world as an open component. --strk; ___ 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] The first release of the PostGIS Add-ons is out!
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Il 20/11/2013 16:32, Rémi Cura ha scritto: There is a very big difference between :' i tweak one of your function, so my tweak must be open source' and 'i use one of your function, so my code must be open source'. Unfortunately GPL imposes both. from my point of view, this is a Very Good Thing - -- Paolo Cavallini - www.faunalia.eu Corsi QGIS e PostGIS: http://www.faunalia.eu/training.html -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.15 (GNU/Linux) Comment: Using GnuPG with Icedove - http://www.enigmail.net/ iEYEARECAAYFAlKM1r4ACgkQ/NedwLUzIr5SaACgoM78J5gKMoo2lFG/Qqx24hCJ UUoAoKgOIIakZ/HvbL94jT4gGVoWFQis =iyTi -END PGP SIGNATURE- ___ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Re: [postgis-users] The first release of the PostGIS Add-ons is out!
On Wed, Nov 20, 2013 at 04:32:12PM +0100, Rémi Cura wrote: There is a very big difference between :' i tweak one of your function, so my tweak must be open source' and 'i use one of your function, so my code must be open source'. Unfortunately GPL imposes both. It's not unfortunate, it's the price some free software authors want to attach to their products. They don't want a profit for themselves but for the community at large, in form of more free software availability. --strk; ___ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Re: [postgis-users] The first release of the PostGIS Add-ons is out!
This is not as simple, Many open source software are not GPL (starting from standard implementing api ), including postgres ! GPL is not always the answer, and here I don't think it is. For me it should be a more open license, and the project should be hosted on the postgis github. Cheers, Rémi-C 2013/11/20 Sandro Santilli s...@keybit.net On Wed, Nov 20, 2013 at 04:32:12PM +0100, Rémi Cura wrote: There is a very big difference between :' i tweak one of your function, so my tweak must be open source' and 'i use one of your function, so my code must be open source'. Unfortunately GPL imposes both. It's not unfortunate, it's the price some free software authors want to attach to their products. They don't want a profit for themselves but for the community at large, in form of more free software availability. --strk; ___ 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] The first release of the PostGIS Add-ons is out!
Hi, I would prefer You just DO WHAT THE FUCK YOU WANT TO as long as it stay open... That's GPL, really. The only reason for people NOT to like it is when they want to craft some piece of closed source and still use what you send out to the world as an open component. Another reason is when the GPL itself is not clear on how it can be applied to some code, because it has been written for compiled code initially. If someone can explain clearly how GPL terms apply to Pl/PgSQL code, I would be glad to hear it. Otherwise it is just adding confusion to something which should be simple, and first people concerned are not proprietary software developers, but opensource developers and wannabe contributors. And last reason is the kind of reason we just had with SFCGAL : incompatibilities between opensource licence. If you begin to annoy opensource developers wanting to do some opensource code, you just reach the opposite goal of your initial intent. This does harm. Vincent ___ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
[postgis-users] simplifying (homogenize) a polygon
Hi all, I am drawing some multipolygons in QGIS and sometimes, I have parts of them which are adjacent and I'd like to homogenize them to have less parts and no adjacent parts. I could do this with a quite complex method: 1. get the number of parts Z: SELECT ST_NumGeometries(geometry) FROMmytable WHERE id=123; 2. do the simplification using ST_CollectionHomogenize: SELECT ST_Multi(ST_CollectionHomogenize(ST_Union(ST_GeometryN(geometry,n FROM mytable CROSS JOIN generate_series(1,Z) n WHERE id = 123; 3. update manually the result in the table (copy paste). Does someone has something more straightforward??? Thanks a lot! Best regards, Denis ___ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Re: [postgis-users] simplifying (homogenize) a polygon
From what I understand of your needs, Postigs topology was designed for this. Cheers, Rémi-C 2013/11/20 Denis Rouzaud denis.rouz...@gmail.com Hi all, I am drawing some multipolygons in QGIS and sometimes, I have parts of them which are adjacent and I'd like to homogenize them to have less parts and no adjacent parts. I could do this with a quite complex method: 1. get the number of parts Z: SELECT ST_NumGeometries(geometry) FROMmytable WHERE id=123; 2. do the simplification using ST_CollectionHomogenize: SELECT ST_Multi(ST_CollectionHomogenize(ST_Union(ST_GeometryN(geometry,n FROM mytable CROSS JOIN generate_series(1,Z) n WHERE id = 123; 3. update manually the result in the table (copy paste). Does someone has something more straightforward??? Thanks a lot! Best regards, Denis ___ 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] The first release of the PostGIS Add-ons is out!
On Nov 20, 2013, at 3:48 PM, Pierre Racine wrote: Salut Pierre, Thanks Vincent for this input. I'll go for GPL for now as it's that a nightmare to change afterward if, really, someone complains. I'm agree with Remi and Vincent points. And i don't see there, any coming code valuable enough to be protected against monsters. Pl/PgSQL codes, as an interpreted language, are not designed to implement real data treatment, and Pl/PgSQL code will be too close to PostgreSQL and PostGIS to be reused in some other project than PostGIS itself So what ? P.S: Anyhow launching a new app with a licence flame could be also a way to make people talk about it... ^^ O. ___ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Re: [postgis-users] Finding Islands
Thank you for these suggestions. I haven't replied yet because I am testing them, and the queries take an hour or more to run on the 2 million plus table. I will report back with some results. On Wed, Nov 20, 2013 at 4:46 AM, Rémi Cura remi.c...@gmail.com wrote: Maybe you can try the stupidest way to go, anyway you have to do a inner product because you have to consider each pair of polygons. You don't have to do inner product. `a LEFT JOIN b ... WHERE b.gid IS NULL` is a typical unmatched query. By doing self join ON ST_Touches(), the polygons with no neighbors are returned because the LEFT JOIN returns all records from a, while ST_Touches() produces no match (because a polygon doesn't touch itself) so b.gid IS NULL. CREATE TABLE result AS SELECT DISTINCT ON (a.gid) a.* FROM table AS a , table AS b WHERE ST_Intersects(a.geom, b.geom) = TRUE AND a.gid != b.gid Just to be clear, this produces the polygons *with* neighbors, which is why I use this as a subselect with gid NOT IN (...) Still testing, --Lee Now you can improve, because in the previous query you will compute (geom1,geom2) and (geom2,geom1), which is duplicate so you can try creating an index on gid , and CREATE TABLE result2 AS WITH direct_comp AS (SELECT a.gid AS agid, a.geom AS ageom , b.gid AS bgid, b.geom AS bgeom FROM table AS a , table AS b WHERE ST_Intersects(a.geom, b.geom) = TRUE AND a.gid b.gid) SELECT agid AS gid, ageom AS geom FROM direct_comp UNION SELECT bgid AS gid, bgeom AS geom FROM direct_comp Now if you want to go really big, this will be difficult. Assuming your polygons bboxes have a homogenous size and/or are not too big reguarding the total area . The idea is to group polygons into same cells, so when you try to find which polygons intersects, instead of comparing a polygon to each other polygon, you compare a polygon to each other polygon in the same cell. create a grid of cells (big enough or you will have lot's of cells) index the table for each cell, get polygons intersecting it. This defines groups of polygons in the same cell, defined by a new id group_id index group_id Now you will have several options depending on your hardware/data 1) process a cell at a time if limited hardware, adding WHERE group_id=XX 2) do a massiv inner join on the group_id and compute all In fact you could avoid to create explicitly the cells, but it will be more complicated. Cheers, Rémi-C 2013/11/20 Brent Wood pcr...@pcreso.com I figure you have spatially indexed the polygons already? Any way of pre-categorising your polygons - binning them in some way that allows a non spatial test in the where clause to replace the spatial test... eg: a boolean attribute set to indicate if a feature has any part = a particular x value or not. run this against the 2m features once to populate it, and assuming you get a 50/50 split of T/F features, your 2m^2 query can instead include a where a.bool = b.bool, as we already know that if the boolean flag is different, they cannot touch, so your query should involve a spatial test only over 1m^2 instead... if you do this on both x y, the boolean filter will replace even more spatial calculations drop them to 500,000^2 tests... If you can pre-classify features so that non-spatial tests can reduce the spatial ones (esp for features with lots of vertices) in a where clause, such queries do run much faster, but you have the trade-off of the time taken to carry out the classification... which is only n*no_classes/2, generally much faster than n^n Hope this makes sense... Brent Wood -- *From:* Lee Hachadoorian lee.hachadooria...@gmail.com *To:* PostGIS Users postgis-us...@postgis.refractions.net *Sent:* Wednesday, November 20, 2013 8:52 PM *Subject:* [postgis-users] Finding Islands I am trying to find islands, polygons in a (multi)polygon layer which are not connected to any other polygons in the same layer. What I came up with runs in a couple of seconds on a layer with ~1000 geometries, and a couple of minutes on a layer with ~23,000 geometries, but I want to run it on a layer of 2 million+ and it's taking a L-O-O-O-NG time, presumably because it's making (2 million)^2 comparisons. What I came up with is: SELECT a.* FROM table a LEFT JOIN table b ON (ST_Touches(a.geom, b.geom)) WHERE b.gid is null or SELECT * FROM table WHERE gid NOT IN ( SELECT DISTINCT a.gid FROM table a JOIN table b ON (ST_Intersects(a.geom, b.geom) AND a.gid != b.gid) ) The first variant raises NOTICE: geometry_gist_joinsel called with incorrect join type. So I thought I could improve performance with an INNER JOIN instead of an OUTER JOIN, and came up with the second variant, and it does seem to perform somewhat better. Any suggestions for how to speed up either approach? Best, --Lee -- Lee Hachadoorian Assistant Professor in Geography, Dartmouth
[postgis-users] Readable reference for - the distance operators
Hi All, What's the best (normal) human readable reference for the PostGIS distance operators-- but deep enough to talk about the use of index structure in the use of the operators? Thanks, Best, Steve ___ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Re: [postgis-users] Readable reference for - the distance operators
Hopefully I'm not too immodest in saying this http://workshops.boundlessgeo.com/postgis-intro/knn.html P On Wed, Nov 20, 2013 at 10:49 AM, Stephen Mather step...@smathermather.com wrote: Hi All, What's the best (normal) human readable reference for the PostGIS distance operators-- but deep enough to talk about the use of index structure in the use of the operators? Thanks, Best, Steve ___ 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] Postgis 2.2.0 With TIGER data
Greg,
Did you run the nation script routine?
http://postgis.net/docs/manual-dev/Loader_Generate_Nation_Script.html
That's the first step needed. Should actually be done before you load the
other tables.
Hope that helps,
Regina
http://www.postgis.us
http://postgis.net
_
From: postgis-users-boun...@lists.osgeo.org
[mailto:postgis-users-boun...@lists.osgeo.org] On Behalf Of Greg Nawrocki
Sent: Wednesday, November 20, 2013 12:10 PM
To: postgis-users@lists.osgeo.org
Subject: [postgis-users] Postgis 2.2.0 With TIGER data
Sorry about the noob question, but I am stuck. I am trying to get to a point
where I can geocode (get latitude and longitude) from an address.
I build and install Postgres 9.3 and Postgis 2.2.0
I create a database named geocode and create the proper extensions,
grants, search paths:
/usr/local/pgsql/bin/psql -d geocode -c CREATE EXTENSION postgis
/usr/local/pgsql/bin/psql -d geocode -c CREATE EXTENSION postgis_topology
/usr/local/pgsql/bin/psql -d geocode -c CREATE EXTENSION fuzzystrmatch;
/usr/local/pgsql/bin/psql -d geocode -c CREATE EXTENSION
postgis_tiger_geocoder;
/usr/local/pgsql/bin/psql -d geocode -c CREATE EXTENSION
address_standardizer;
/usr/local/pgsql/bin/psql geocode -f
/usr/local/pgsql/share/contrib/postgis-2.2/legacy.sql
/usr/local/pgsql/bin/psql -d geocode -c GRANT ALL ON geometry_columns TO
PUBLIC;
/usr/local/pgsql/bin/psql -d geocode -c GRANT ALL ON geography_columns TO
PUBLIC;
/usr/local/pgsql/bin/psql -d geocode -c GRANT ALL ON spatial_ref_sys TO
PUBLIC;
/usr/local/pgsql/bin/psql -d geocode -c ALTER DATABASE geocode SET
search_path=public, tiger;
Here's some information on the resulting environment:
=
SELECT version();
version
--
PostgreSQL 9.3.0 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.7.2
20120921 (Red Hat 4.7.2-2), 64-bit
(1 row)
SELECT postgis_full_version();
postgis_full_version
-
POSTGIS=2.2.0dev r12128 GEOS=3.3.8-CAPI-1.7.8 PROJ=Rel. 4.8.0, 6 March
2012 GDAL=GDAL 1.9.1, released 2012/05/15 LIBXML=2.7.8 TOPOLOGY RASTER
(1 row)
SELECT name, default_version,installed_version FROM pg_available_extensions
WHERE name LIKE 'postgis%';
name | default_version | installed_version
+-+---
postgis_tiger_geocoder | 2.2.0dev| 2.2.0dev
postgis| 2.2.0dev| 2.2.0dev
postgis_topology | 2.2.0dev| 2.2.0dev
(3 rows)
==
The pprint_addy() and normalize_address() functions work as expected in the
examples I've seen.
I generate, modify and run a tiger load script to retrieve TIGER data for my
state:
/usr/local/pgsql/bin/psql -d geocode -c SELECT
loader_generate_script(ARRAY['IL'], 'sh'); -A -o ILdata.sh
Everything appears to load normally. The only anomaly I see is the following
message when installing the various featnames.dbf files:
XXX_featnames.dbf: shape (.shp) or index files (.shx) can not be opened,
will just import attribute data.
However, from what I can see those files contain no shape information so I
do not think this is an error.
I run the install_missing_indexes() function which appears to work.
However, when I attempt to geocode I get an immediate empty return.
/usr/local/pgsql/bin/psql -d geocode -c SELECT g.rating, ST_X(g.geomout) As
lon, ST_Y(g.geomout) As lat FROM geocode('810 W Main St, West Dundee, IL
60118') As g;
rating | lon | lat
+-+-
(0 rows)
At this point, I'm lost. Any help greatly appreciated.
-- Greg
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Re: [postgis-users] Readable reference for - the distance operators
No, that is in fact excellent (actually irritatingly good, I was just finishing up a write up of my own...), but I should have been more specific. I'm looking for a reference to point to for the why it works, not the how, i.e. how does the structure of the R-Tree index lends itself to creating a distance operator -- the hierarchy of the tree, the distance between branches, or what not. Best, Steve On Wed, Nov 20, 2013 at 2:51 PM, Paul Ramsey pram...@cleverelephant.cawrote: Hopefully I'm not too immodest in saying this http://workshops.boundlessgeo.com/postgis-intro/knn.html P On Wed, Nov 20, 2013 at 10:49 AM, Stephen Mather step...@smathermather.com wrote: Hi All, What's the best (normal) human readable reference for the PostGIS distance operators-- but deep enough to talk about the use of index structure in the use of the operators? Thanks, Best, Steve ___ 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] Postgis 2.2.0 With TIGER data
Yes! Thanks Regina,
You know, I kept looking at that function and convinced myself that it was
for loading the entire country as opposed to individual states.
I'm getting Lat / Long now.
-- Greg
From: Paragon Corporation l...@pcorp.us
Reply-To: PostGIS Users Discussion postgis-users@lists.osgeo.org
Date: Wednesday, November 20, 2013 2:27 PM
To: 'PostGIS Users Discussion' postgis-users@lists.osgeo.org
Subject: Re: [postgis-users] Postgis 2.2.0 With TIGER data
Greg,
Did you run the nation script routine?
http://postgis.net/docs/manual-dev/Loader_Generate_Nation_Script.html
That's the first step needed. Should actually be done before you load the
other tables.
Hope that helps,
Regina
http://www.postgis.us
http://postgis.net
From: postgis-users-boun...@lists.osgeo.org
[mailto:postgis-users-boun...@lists.osgeo.org] On Behalf Of Greg Nawrocki
Sent: Wednesday, November 20, 2013 12:10 PM
To: postgis-users@lists.osgeo.org
Subject: [postgis-users] Postgis 2.2.0 With TIGER data
Sorry about the noob question, but I am stuck. I am trying to get to a point
where I can geocode (get latitude and longitude) from an address.
I build and install Postgres 9.3 and Postgis 2.2.0
I create a database named geocode and create the proper extensions,
grants, search paths:
/usr/local/pgsql/bin/psql -d geocode -c CREATE EXTENSION postgis
/usr/local/pgsql/bin/psql -d geocode -c CREATE EXTENSION postgis_topology
/usr/local/pgsql/bin/psql -d geocode -c CREATE EXTENSION fuzzystrmatch;
/usr/local/pgsql/bin/psql -d geocode -c CREATE EXTENSION
postgis_tiger_geocoder;
/usr/local/pgsql/bin/psql -d geocode -c CREATE EXTENSION
address_standardizer;
/usr/local/pgsql/bin/psql geocode -f
/usr/local/pgsql/share/contrib/postgis-2.2/legacy.sql
/usr/local/pgsql/bin/psql -d geocode -c GRANT ALL ON geometry_columns TO
PUBLIC;
/usr/local/pgsql/bin/psql -d geocode -c GRANT ALL ON geography_columns TO
PUBLIC;
/usr/local/pgsql/bin/psql -d geocode -c GRANT ALL ON spatial_ref_sys TO
PUBLIC;
/usr/local/pgsql/bin/psql -d geocode -c ALTER DATABASE geocode SET
search_path=public, tiger;
Here's some information on the resulting environment:
=
SELECT version();
version
--
PostgreSQL 9.3.0 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.7.2
20120921 (Red Hat 4.7.2-2), 64-bit
(1 row)
SELECT postgis_full_version();
postgis_full_version
-
POSTGIS=2.2.0dev r12128 GEOS=3.3.8-CAPI-1.7.8 PROJ=Rel. 4.8.0, 6 March
2012 GDAL=GDAL 1.9.1, released 2012/05/15 LIBXML=2.7.8 TOPOLOGY RASTER
(1 row)
SELECT name, default_version,installed_version FROM pg_available_extensions
WHERE name LIKE 'postgis%';
name | default_version | installed_version
+-+---
postgis_tiger_geocoder | 2.2.0dev| 2.2.0dev
postgis| 2.2.0dev| 2.2.0dev
postgis_topology | 2.2.0dev| 2.2.0dev
(3 rows)
==
The pprint_addy() and normalize_address() functions work as expected in the
examples I've seen.
I generate, modify and run a tiger load script to retrieve TIGER data for my
state:
/usr/local/pgsql/bin/psql -d geocode -c SELECT
loader_generate_script(ARRAY['IL'], 'sh'); -A -o ILdata.sh
Everything appears to load normally. The only anomaly I see is the following
message when installing the various featnames.dbf files:
XXX_featnames.dbf: shape (.shp) or index files (.shx) can not be opened,
will just import attribute data.
However, from what I can see those files contain no shape information so I
do not think this is an error.
I run the install_missing_indexes() function which appears to work.
However, when I attempt to geocode I get an immediate empty return.
/usr/local/pgsql/bin/psql -d geocode -c SELECT g.rating, ST_X(g.geomout) As
lon, ST_Y(g.geomout) As lat FROM geocode('810 W Main St, West Dundee, IL
60118') As g;
rating | lon | lat
+-+-
(0 rows)
At this point, I'm lost Any help greatly appreciated.
-- Greg
___ 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] simplifying (homogenize) a polygon
Hello Rémi, I was hoping a simplest request without enabling topology but thanks anyway! Cheers, Denis On 20. 11. 13 18:26, Rémi Cura wrote: From what I understand of your needs, Postigs topology was designed for this. Cheers, Rémi-C 2013/11/20 Denis Rouzaud denis.rouz...@gmail.com mailto:denis.rouz...@gmail.com Hi all, I am drawing some multipolygons in QGIS and sometimes, I have parts of them which are adjacent and I'd like to homogenize them to have less parts and no adjacent parts. I could do this with a quite complex method: 1. get the number of parts Z: SELECT ST_NumGeometries(geometry) FROMmytable WHERE id=123; 2. do the simplification using ST_CollectionHomogenize: SELECT ST_Multi(ST_CollectionHomogenize(ST_Union(ST_GeometryN(geometry,n FROM mytable CROSS JOIN generate_series(1,Z) n WHERE id = 123; 3. update manually the result in the table (copy paste). Does someone has something more straightforward??? Thanks a lot! Best regards, Denis ___ postgis-users mailing list postgis-users@lists.osgeo.org mailto: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
