Re: [postgis-users] Can you rebuild a multipolygon from rings?
On Fri, Oct 21, 2011 at 01:33:43PM +0800, Ben Madin wrote: But rebuilding the multipolygons is not so simple (for me) - I need to re-aggregate the remaining rings into a geometry dump to reinsert into the other dumped polygons to rebuild the multipolygon. ... insert into phl (the_geom) select ((st_dump(the_geom))) from summ.ctybnda where year=2005 and ccode like 'PHL'; ... insert into phl2 (the_geom) select st_dumprings(((the_geom).geom)) from phl where id = 2601; You're stripping out the path component of output from st_dump and st_dumprings. Those are the keys to your later rebuilding. --strk; () Free GIS Flash consultant/developer /\ http://strk.keybit.net/services.html ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Indexes used in 1.3.3 but not in 1.5.3
I've tried to increase the cost of _ST_Buffer and _ST_Overlaps function but without success. Then, I've tried to lower the cost of ST_Buffer to 1 and the index is used. Now I'll try to see if it works for others queries (stored in views in my database). Many thanks for your hints Regina, it helps a lot ! fredj On Thu, Oct 20, 2011 at 10:42 PM, Paragon Corporation l...@pcorp.us wrote: Fred, Try increasing the cost of _st_overlaps and _st_buffer so it doesn't know using these is more costly than the index or the index costs we have may be messed up in some way. There is a report that we might have these set too low. I haven't done enough analysis to know wha the right costing would be. http://trac.osgeo.org/postgis/ticket/1248 --- CREATE OR REPLACE FUNCTION _st_covers(geometry, geometry) RETURNS boolean AS '$libdir/postgis-1.5', 'covers' LANGUAGE c IMMUTABLE STRICT COST 800; CREATE OR REPLACE FUNCTION _st_buffer(geometry, double precision, cstring) RETURNS geometry AS '$libdir/postgis-1.5', 'buffer' LANGUAGE c IMMUTABLE STRICT COST 1000; You might want to fiddle with the numbers a bit. Newer versions of PostgreSQL might be doing something different with the costs than prior or weighting gist index costs higher than before. I believe the cost settings came out in 8.3 so your old should be using costing metrics as well so not sure why it would switch between now. Hope that helps, Regina http://www.postgis.us -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Frederic Junod Sent: Thursday, October 20, 2011 11:32 AM To: postgis-users@postgis.refractions.net Subject: [postgis-users] Indexes used in 1.3.3 but not in 1.5.3 Hello, With postgis 1.3.3 on postgresql 8.3.14, the following select uses the indexes: EXPLAIN SELECT 1 FROM grid_pk25, grid_geocover WHERE st_overlaps(st_buffer(grid_geocover.the_geom, -1), grid_pk25.the_geom); QUERY PLAN Nested Loop (cost=0.00..128.61 rows=1 width=0) Join Filter: _st_overlaps(st_buffer(grid_geocover.the_geom, (-1)::double precision), grid_pk25.the_geom) - Seq Scan on grid_geocover (cost=0.00..8.22 rows=222 width=813) - Index Scan using grid_pk25_the_geom_1150804304770 on grid_pk25 (cost=0.00..0.52 rows=1 width=1217) Index Cond: (st_buffer(grid_geocover.the_geom, (-1)::double precision) grid_pk25.the_geom) Filter: (st_buffer(grid_geocover.the_geom, (-1)::double precision) grid_pk25.the_geom) The same query, with postgis 1.5.3 on postgresql 9.0.4 do not uses the indexes: QUERY PLAN --- Nested Loop (cost=0.00..29606.88 rows=19240 width=0) Join Filter: st_overlaps(st_buffer(grid_geocover.the_geom, (-1)::double precision), grid_pk25.the_geom) - Seq Scan on grid_pk25 (cost=0.00..15.60 rows=260 width=1217) - Materialize (cost=0.00..10.33 rows=222 width=813) - Seq Scan on grid_geocover (cost=0.00..9.22 rows=222 width=813) (5 rows) The database was imported with postgis_restore.pl. I've also tried to reindex the database. Note that on both instances, if the st_buffer is removed the indexes are used. What's happening ? Regards, fredj -- Frédéric Junod Camptocamp SA ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users -- Frédéric Junod Camptocamp SA ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Can you rebuild a multipolygon from rings?
Thanks strk,
but I'm still confused. If I don't select the .geom component, I can't
dumprings... when I do, I end up with 90 rows in a table of geometry dumps.
# select id, (the_geom).path, st_astext((the_geom).geom) from phl2 where id =
58;
id | path | st_astext
+--+--
58 | {57} | POLYGON((126.023966 8.568675,126.023895 8.56901,126.013809
8.570289,126.023912 8.56893,126.023966 8.568675))
(1 row)
# select id, ((the_geom).path), st_isvalidreason((the_geom).geom) from phl2
where st_isvalid((the_geom).geom) is false;
NOTICE: Self-intersection at or near point 126.024 8.56893
id | path | st_isvalidreason
+--+-
58 | {57} | Self-intersection[126.02391195391 8.56893000619979]
which is the polygon ring I want to delete
I can delete it, but I now want to insert this polygon (the remaining 89 rows)
back into my table of polgons (the dump from the first step), and then turn
them back into a multipolygon. In fact, I can do the last step. But I can't get
the geometry dump back into the polygons table in geometry dump format.
Maybe I'm trying to do this wrong, but there must be a function I haven't found?
cheers
Ben
On 21/10/2011, at 3:17 PM, Sandro Santilli wrote:
On Fri, Oct 21, 2011 at 01:33:43PM +0800, Ben Madin wrote:
But rebuilding the multipolygons is not so simple (for me) - I need to
re-aggregate the remaining rings into a geometry dump to reinsert into the
other dumped polygons to rebuild the multipolygon.
...
insert into phl (the_geom) select ((st_dump(the_geom))) from summ.ctybnda
where year=2005 and ccode like 'PHL';
...
insert into phl2 (the_geom) select st_dumprings(((the_geom).geom)) from phl
where id = 2601;
You're stripping out the path component of output from st_dump
and st_dumprings. Those are the keys to your later rebuilding.
--strk;
() Free GIS Flash consultant/developer
/\ http://strk.keybit.net/services.html
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Convert Lat/Lon/Alt to ECFCoordinates
Is there an easy way to convert data stored in lat/lon/alt to ECF coordinates? ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] postgis-1.4.so for pg_upgrade PostgreSQL 8.4.1 to9.0.4
I have run the postgis_upgrade_14_to_15.sql against the 8.4.1 database and installed postgis-1.5.3 into the 9.0.4 database. Is anything else required before running pg_upgrade? Cheers and thanks, Stephen On Friday 21 October 2011 11:16:47 Paragon Corporation wrote: Steve, Haven't tried myself. Off-hand I don't see why there would be an issue since the on disk structure didn't change between 1.4 and 1.5. At anyrate, probably the first thing you want to do is run the postgis 1.4-1_5 upgrade -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Stephen Davies Sent: Thursday, October 20, 2011 8:37 PM To: postgis-users@postgis.refractions.net Subject: [postgis-users] postgis-1.4.so for pg_upgrade PostgreSQL 8.4.1 to9.0.4 I do not have enough disk space to use dump/reload to migrate an 8.4.1 spatial database to 9.0.4 so hope to use pg_upgrade. However, to do that, it seems that I need to have a 9.0.4 version of postgis-1.4.so: | Your installation references loadable libraries that are missing from | the new installation. You can add these libraries to the new | installation, or remove the functions using them from the old | installation. A list of the problem libraries is in the file | /home/postgres/loadable_libraries.txt. [postgres@mustang ~]$ cat loadable_libraries.txt Failed to load library: $libdir/postgis-1.4 ERROR: could not access file $libdir/postgis-1.4: No such file or directory If I try to create a 9.0.4 version of postgis-1.4.so, I get: make[1]: Entering directory `/var/sources/postgis-1.4.0/postgis' gcc -E -traditional-cpp postgis.sql.in.c | grep -v '^#' postgis.sql.in sed 's,MODULE_PATHNAME,$libdir/postgis-1.4,g' postgis.sql.in postgis.sql gcc -E -traditional-cpp uninstall_postgis.sql.in.c | grep -v '^#' uninstall_postgis.sql.in sed 's,MODULE_PATHNAME,$libdir/uninstall_postgis-1.4,g' uninstall_postgis.sql.in uninstall_postgis.sql /usr/bin/perl ../utils/postgis_proc_upgrade.pl postgis.sql postgis_upgrade.sql gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after- statement -Wendif-labels -fno-strict-aliasing -fwrapv -fpic -I/usr/include - I../liblwgeom -I. -I. -I/usr/local/pgsql904/include/server - I/usr/local/pgsql904/include/internal -D_GNU_SOURCE -c -o lwgeom_pg.o lwgeom_pg.c gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after- statement -Wendif-labels -fno-strict-aliasing -fwrapv -fpic -I/usr/include - I../liblwgeom -I. -I. -I/usr/local/pgsql904/include/server - I/usr/local/pgsql904/include/internal -D_GNU_SOURCE -c -o lwgeom_debug.o lwgeom_debug.c gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after- statement -Wendif-labels -fno-strict-aliasing -fwrapv -fpic -I/usr/include - I../liblwgeom -I. -I. -I/usr/local/pgsql904/include/server - I/usr/local/pgsql904/include/internal -D_GNU_SOURCE -c -o lwgeom_accum.o lwgeom_accum.c lwgeom_accum.c: In function 'pgis_geometry_accum_transfn': lwgeom_accum.c:110: error: 'WindowAggState' has no member named 'wincontext' make[1]: *** [lwgeom_accum.o] Error 1 make[1]: Leaving directory `/var/sources/postgis-1.4.0/postgis' make: *** [postgis] Error 2 So I then made a symlink from my 9.0.4 postgis-1.5.so to postgis-1.4.so and pg_upgrade was happy: *Clusters are compatible* Is it safe to proceed with this setup? Cheers and thanks, Stephen -- == === Stephen Davies Consulting P/L Voice: 08-8177 1595 Adelaide, South Australia.Fax : 08-8177 0133 Records Collections Management. Mobile:040 304 0583 ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users -- = Stephen Davies Consulting P/L Voice: 08-8177 1595 Adelaide, South Australia.Fax : 08-8177 0133 Records Collections Management. Mobile:040 304 0583 ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
