[postgis-users] Can you rebuild a multipolygon from rings?
G'day all, We have a problem with erroneous geometries that we can't edit using QGIS - the geometries are too big, and the application just hangs. The geometries are multipolygons, and they contain invalid rings. To get at them using postgis seems to require two levels of dumping. First, st_dump to split the Multipolygons into polygons, then st_dumprings to get at the bad ring. 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. I'm not sure how to express this more articulately. sorry. I have done : create table phl(id serial unique, country varchar not null default 'tmp', the_geom geometry_dump); insert into phl (the_geom) select ((st_dump(the_geom))) from summ.ctybnda where year=2005 and ccode like 'PHL'; Then I find the bad geometry : select id, ((the_geom).path), st_isvalid((the_geom).geom) from phl where st_isvalid((the_geom).geom) is false; create table phl2(id serial unique, country varchar not null default 'tmp', the_geom geometry_dump); insert into phl2 (the_geom) select st_dumprings(((the_geom).geom)) from phl where id = 2601; select id, ((the_geom).path), st_isvalid((the_geom).geom) from phl2 where st_isvalid((the_geom).geom) is false; And Delete it delete from phl2 where id = 58; Now is where I'm stuck - I need to congeal the remaining rings back into the first table, replacing the polygon that was unhealthy, but I have a geometry_dump, and need to aggregate it into another geometry dump to fit into the table... I can happily aggregate geometry dumps into geometries, but I don't seem to be able to collect my rings back into a geometry dump. This works to rebuild the original multipolygon from the dumped polygons table : update summ.ctybnda c set the_geom = (select st_multi(st_collect(n.the_geom)) from (select country, ((p.the_geom).geom) as the_geom from phl p) as n) where c.year = 2005 and c.ccode = 'PHL'; but I can't get my repaired polygon back into the dumped polygons table. Any idea? cheers Ben ___ 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
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 > ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Tiger Geocoder: SELECT loader_generate_script( --all states -- , 'sh') ??
Thanks Regina!
On 2011-10-20, at 6:04 PM, Paragon Corporation wrote:
> […]
> As far as short-hand for all states. You could do
>
> SELECT loader_generate_script(ARRAY(SELECT abbrev FROM state_lookup ORDER BY
> abbrev), 'windows');
>
> That still isn't perfect since you get duplicate path settings in each
> script. We had designed it that way with the idea of being able to
> parallelly load the states, but the tear down of the staging schema makes
> that not work quite right. It wouldn't require too much effort to change,
> but probably not that badly needed by most people especially if you have
> slow netowrk speed or just single data disk drive.
I was just lazy.
SELECT loader_generate_script(ARRAY['AK', 'AL', 'AR', 'AZ', 'CA', 'CO', 'CT',
'DC', 'DE', 'FL', 'GA', 'HI', 'IA', 'ID', 'IL', 'IN', 'KS', 'KY', 'LA', 'MA',
'MD', 'ME', 'MI', 'MN', 'MO', 'MS', 'MT', 'NC', 'ND', 'NE', 'NH', 'NJ', 'NM',
'NV', 'NY', 'OH', 'OK', 'OR', 'PA', 'RI', 'SC', 'SD', 'TN', 'TX', 'UT', 'VA',
'VT', 'WA', 'WI', 'WV', 'WY'], 'sh');
>
> If you are interested, put in a ticket for an ALL States option that will
> create just one script with all states.
>
> http://trac.osgeo.org/postgis/
>
> Put it under the component tiger_geocoder
>
> And it will be assigned to me.
Will do. I have to say, great work on all of this. It's very impressive. The
[small] bug I encountered was from the generated loader script, around line 18:
${PSQL} -c "DROP SCHEMA tiger_staging CASCADE;"
This returns an error from Postgresql (owing to "set -e -u") since when the
script first runs there is no tiger_staging. Because the script is told to stop
on any error, it will halt here (or it does for me). In spite of my usual
helplessness, I got past this simply by changing it to:
${PSQL} -c "DROP SCHEMA IF EXISTS tiger_staging CASCADE;"
…Rene
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] postgis-1.4.so for pg_upgrade PostgreSQL 8.4.1 to 9.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
Re: [postgis-users] Tiger Geocoder: SELECT loader_generate_script( -- all states -- , 'sh') ??
Steve, As mentioned in other note. We already do. If René is seeing something else, then something is wrong with his install. -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Stephen Woodbridge Sent: Thursday, October 20, 2011 7:52 PM To: postgis-users@postgis.refractions.net Subject: Re: [postgis-users] Tiger Geocoder: SELECT loader_generate_script( -- all states -- , 'sh') ?? On 10/20/2011 6:22 PM, René Fournier wrote: > This: > > SELECT loader_generate_script(ARRAY['DC','RI'], 'windows'); > > returns a shell script that will wget the Tiger data for the states > of DC and Rhode Island. And, aside from a couple small bugs, works > very well. I was wondering if there were a shorthand for generating a > shell script that will fetch ALL STATES? > > On a related note: These scripts appear to consolidate all the states > into the same tables -- I was wondering if splitting them up into > separate state-specific tables would improve performance? Anyone try > it? It might be interesting to use inherited tables where each state is in it's own table then use table constraints to select which tables need to be used or excluded. -Steve ___ 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
Re: [postgis-users] Tiger Geocoder: SELECT loader_generate_script( --all states -- , 'sh') ??
They don't consolidate into same tables - each state is put in a separate table in tiger_data schema, which inherit from the similarly named tables in tiger schema. So for example In tiger_data you will see ri_edges, ri_county etc. Which inherit from the tables in Tiger.edges Tiger.county So although the code references the parent tables, the paraent table have no data in them. This is to allow further partitioning or consolidation without having to rewrite the code. Also allows for easier maintenance (which is our primary reason for breaking it up this way) since to replace a state table, you can take it offline by uninheriting it and then put it back when you are done making changes with inherit. You can also in hteroy do parallel state loads with this model more easily and put your favorite states on faster disks. As far as short-hand for all states. You could do SELECT loader_generate_script(ARRAY(SELECT abbrev FROM state_lookup ORDER BY abbrev), 'windows'); That still isn't perfect since you get duplicate path settings in each script. We had designed it that way with the idea of being able to parallelly load the states, but the tear down of the staging schema makes that not work quite right. It wouldn't require too much effort to change, but probably not that badly needed by most people especially if you have slow netowrk speed or just single data disk drive. If you are interested, put in a ticket for an ALL States option that will create just one script with all states. http://trac.osgeo.org/postgis/ Put it under the component tiger_geocoder And it will be assigned to me. Thanks, Regina http://www.postgis.us -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of René Fournier Sent: Thursday, October 20, 2011 6:22 PM To: PostGIS Users Discussion Subject: [postgis-users] Tiger Geocoder: SELECT loader_generate_script( --all states -- , 'sh') ?? This: SELECT loader_generate_script(ARRAY['DC','RI'], 'windows'); returns a shell script that will wget the Tiger data for the states of DC and Rhode Island. And, aside from a couple small bugs, works very well. I was wondering if there were a shorthand for generating a shell script that will fetch ALL STATES? On a related note: These scripts appear to consolidate all the states into the same tables -- I was wondering if splitting them up into separate state-specific tables would improve performance? Anyone try it? Rene ___ 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
Re: [postgis-users] Tiger Geocoder: SELECT loader_generate_script( -- all states -- , 'sh') ??
On 10/20/2011 6:22 PM, René Fournier wrote: This: SELECT loader_generate_script(ARRAY['DC','RI'], 'windows'); …returns a shell script that will wget the Tiger data for the states of DC and Rhode Island. And, aside from a couple small bugs, works very well. I was wondering if there were a shorthand for generating a shell script that will fetch ALL STATES? On a related note: These scripts appear to consolidate all the states into the same tables -- I was wondering if splitting them up into separate state-specific tables would improve performance? Anyone try it? It might be interesting to use inherited tables where each state is in it's own table then use table constraints to select which tables need to be used or excluded. -Steve ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Tiger Geocoder: SELECT loader_generate_script( -- all states -- , 'sh') ??
This: SELECT loader_generate_script(ARRAY['DC','RI'], 'windows'); …returns a shell script that will wget the Tiger data for the states of DC and Rhode Island. And, aside from a couple small bugs, works very well. I was wondering if there were a shorthand for generating a shell script that will fetch ALL STATES? On a related note: These scripts appear to consolidate all the states into the same tables -- I was wondering if splitting them up into separate state-specific tables would improve performance? Anyone try it? …Rene ___ 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
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
[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
Re: [postgis-users] FW: Support in ST_ConvexHull syntax
Now I'm trying to form the polygon but with ST_ConcaveHull and 80% but this error message appears: *** ERROR: function st_concavehull(geometry, numeric) does not exist LINE 1: INSERT INTO splitbeam_area_polygon (geom) SELECT ST_ConcaveH... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. *** I have no problems with ConvexHull, it created the polygon with "INSERT INTO splitbeam_area_polygon (geom) SELECT ST_ConvexHull(ST_Collect(geom)) AS geom FROM splitbeam_point;" But this gives the above error: INSERT INTO splitbeam_area_polygon (geom) SELECT ST_ConcaveHull(ST_Collect(geom), 0.80) AS geom FROM splitbeam_point; I'm using PostGis 1.53 and PostgreSQL 8.4.8. Any hint is very grateful, Thanks in advance, Gery > Date: Thu, 20 Oct 2011 11:53:27 +0200 > From: s...@keybit.net > To: gameji...@hotmail.com > CC: postgis-users@postgis.refractions.net > Subject: Re: [postgis-users] FW: Support in ST_ConvexHull syntax > > On Thu, Oct 20, 2011 at 09:26:48AM +, Gery . wrote: > > > mop=# \d unaprueba > >Table "public.unaprueba" > > Column | Type | Modifiers > > +--+--- > > id | smallint | > > geom | geometry | > > Indexes: > > "unaprueba_geom" gist (geom) > > Check constraints: > > "enforce_dims_geom" CHECK (st_ndims(geom) = 2) > > "enforce_geotype_geom" CHECK (geometrytype(geom) = 'POLYGON'::text OR > > geom IS NULL) > > "enforce_srid_geom" CHECK (st_srid(geom) = 4326) > > ... > > > mop=# insert into unaprueba (geom) SELECT st_convexhull(st_collect(geom)) > > as geom FROM splitbeam_point ; > > ERROR: new row for relation "unaprueba" violates check constraint > > "enforce_dims_geom" > > > > where could be the problem? > > << The new row violates check constraint "enforce_dims_geom" >> > > Compare the above message with the output of \d above: > > > Check constraints: > > "enforce_dims_geom" CHECK (st_ndims(geom) = 2) > ^^^ > > "enforce_geotype_geom" CHECK (geometrytype(geom) = 'POLYGON'::text OR > > geom IS NULL) > > Check the output of: > > st_ndims(st_convexhull(st_collect(geom)) from splitbeam_point; > > Must be 2 ... > > --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] FW: Support in ST_ConvexHull syntax
you're a genius man, my mistake again, my input is 3D point, so, the polygon should be 3D also, thanks man. > Date: Thu, 20 Oct 2011 14:56:24 +0200 > From: s...@keybit.net > To: gameji...@hotmail.com > CC: postgis-users@postgis.refractions.net > Subject: Re: [postgis-users] FW: Support in ST_ConvexHull syntax > > On Thu, Oct 20, 2011 at 12:09:01PM +, Gery . wrote: > > > > > > or should be 3 in my table! Bingo! after running: "select > > st_ndims(st_convexhull(st_collect(geom)) from splitbeam_point;" I got 3 > > instead of 2, so I put this3 into my table and ready! that did the trick. > > Now I'm wondering why this has to be 3, I mean, this is a 2D polygon, I > > think I'll realize that after keeping reading. > > > > Thanks again Sandro, you were very helpful, > > You could have forced your output to 2d using ST_Force_2d, > if that's what you needed. > > Why you get a 3d convexhull from a 2d input I don't know. > Are you sure your input geometries are 2d ? > Check st_ndims against all stages: geom, st_collect(geom) > > --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] FW: Support in ST_ConvexHull syntax
On Thu, Oct 20, 2011 at 12:09:01PM +, Gery . wrote: > > > or should be 3 in my table! Bingo! after running: "select > st_ndims(st_convexhull(st_collect(geom)) from splitbeam_point;" I got 3 > instead of 2, so I put this3 into my table and ready! that did the trick. Now > I'm wondering why this has to be 3, I mean, this is a 2D polygon, I think > I'll realize that after keeping reading. > > Thanks again Sandro, you were very helpful, You could have forced your output to 2d using ST_Force_2d, if that's what you needed. Why you get a 3d convexhull from a 2d input I don't know. Are you sure your input geometries are 2d ? Check st_ndims against all stages: geom, st_collect(geom) --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] FW: Support in ST_ConvexHull syntax
or should be 3 in my table! Bingo! after running: "select st_ndims(st_convexhull(st_collect(geom)) from splitbeam_point;" I got 3 instead of 2, so I put this3 into my table and ready! that did the trick. Now I'm wondering why this has to be 3, I mean, this is a 2D polygon, I think I'll realize that after keeping reading. Thanks again Sandro, you were very helpful, Cheers, Gery > Date: Thu, 20 Oct 2011 11:53:27 +0200 > From: s...@keybit.net > To: gameji...@hotmail.com > CC: postgis-users@postgis.refractions.net > Subject: Re: [postgis-users] FW: Support in ST_ConvexHull syntax > > On Thu, Oct 20, 2011 at 09:26:48AM +, Gery . wrote: > > > mop=# \d unaprueba > >Table "public.unaprueba" > > Column | Type | Modifiers > > +--+--- > > id | smallint | > > geom | geometry | > > Indexes: > > "unaprueba_geom" gist (geom) > > Check constraints: > > "enforce_dims_geom" CHECK (st_ndims(geom) = 2) > > "enforce_geotype_geom" CHECK (geometrytype(geom) = 'POLYGON'::text OR > > geom IS NULL) > > "enforce_srid_geom" CHECK (st_srid(geom) = 4326) > > ... > > > mop=# insert into unaprueba (geom) SELECT st_convexhull(st_collect(geom)) > > as geom FROM splitbeam_point ; > > ERROR: new row for relation "unaprueba" violates check constraint > > "enforce_dims_geom" > > > > where could be the problem? > > << The new row violates check constraint "enforce_dims_geom" >> > > Compare the above message with the output of \d above: > > > Check constraints: > > "enforce_dims_geom" CHECK (st_ndims(geom) = 2) > ^^^ > > "enforce_geotype_geom" CHECK (geometrytype(geom) = 'POLYGON'::text OR > > geom IS NULL) > > Check the output of: > > st_ndims(st_convexhull(st_collect(geom)) from splitbeam_point; > > Must be 2 ... > > --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] FW: Support in ST_ConvexHull syntax
On Thu, Oct 20, 2011 at 09:26:48AM +, Gery . wrote: > mop=# \d unaprueba > Table "public.unaprueba" > Column | Type | Modifiers > +--+--- > id | smallint | > geom | geometry | > Indexes: > "unaprueba_geom" gist (geom) > Check constraints: > "enforce_dims_geom" CHECK (st_ndims(geom) = 2) > "enforce_geotype_geom" CHECK (geometrytype(geom) = 'POLYGON'::text OR > geom IS NULL) > "enforce_srid_geom" CHECK (st_srid(geom) = 4326) ... > mop=# insert into unaprueba (geom) SELECT st_convexhull(st_collect(geom)) as > geom FROM splitbeam_point ; > ERROR: new row for relation "unaprueba" violates check constraint > "enforce_dims_geom" > > where could be the problem? << The new row violates check constraint "enforce_dims_geom" >> Compare the above message with the output of \d above: > Check constraints: > "enforce_dims_geom" CHECK (st_ndims(geom) = 2) ^^^ > "enforce_geotype_geom" CHECK (geometrytype(geom) = 'POLYGON'::text OR > geom IS NULL) Check the output of: st_ndims(st_convexhull(st_collect(geom)) from splitbeam_point; Must be 2 ... --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] FW: Support in ST_ConvexHull syntax
Hello Sandro, Thanks and yes my mistake, I have 2 tables by mistake, ie., 'unaprueba' and 'testone', the first one is the right one, this is what I get after your suggestion: mop=# \d unaprueba Table "public.unaprueba" Column | Type | Modifiers +--+--- id | smallint | geom | geometry | Indexes: "unaprueba_geom" gist (geom) Check constraints: "enforce_dims_geom" CHECK (st_ndims(geom) = 2) "enforce_geotype_geom" CHECK (geometrytype(geom) = 'POLYGON'::text OR geom IS NULL) "enforce_srid_geom" CHECK (st_srid(geom) = 4326) mop=# SELECT st_geometrytype(st_convexhull(st_collect(geom))) from splitbeam_point ; st_geometrytype - ST_Polygon (1 row) So, I think everything should be right, I dont know why this commad doesnt work: mop=# insert into unaprueba (geom) SELECT st_convexhull(st_collect(geom)) as geom FROM splitbeam_point ; ERROR: new row for relation "unaprueba" violates check constraint "enforce_dims_geom" where could be the problem? Gery > Date: Thu, 20 Oct 2011 10:15:41 +0200 > From: s...@keybit.net > To: postgis-users@postgis.refractions.net > CC: gameji...@hotmail.com > Subject: Re: [postgis-users] FW: Support in ST_ConvexHull syntax > > On Wed, Oct 19, 2011 at 06:03:27PM +, Gery . wrote: > > > mop=# insert into testone (geom) SELECT ST_ConvexHull(ST_Collect(geom)) as > > geom FROM splitbeam_point ; > > ERROR: new row for relation "unaprueba" violates check constraint > > "enforce_geotype_geom" > > > > where could be the error? any hint is very grateful, thanks in advance > > The error says that the "geom" field of "unaprueba" table is required > to be a geometry of a given type and you're trying to insert one of > a different type instead. > > -- shows what type is expected by the table: > \d unaprueba > > -- shows what you're trying to insert: > SELECT ST_GeometryType( > ST_ConvexHull(ST_Collect(geom)) FROM splitbeam_point ; > ); > > --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] Postgis 1.5.3 regression test failures - fixed
On Thu, Oct 20, 2011 at 01:53:03PM +1030, Stephen Davies wrote: > The "rounding error" in test 47 remains. Has this been fixed yet? I belive it was fixed in trunk. Not sure about 1.5 branch. --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] FW: Support in ST_ConvexHull syntax
On Wed, Oct 19, 2011 at 06:03:27PM +, Gery . wrote: > mop=# insert into testone (geom) SELECT ST_ConvexHull(ST_Collect(geom)) as > geom FROM splitbeam_point ; > ERROR: new row for relation "unaprueba" violates check constraint > "enforce_geotype_geom" > > where could be the error? any hint is very grateful, thanks in advance The error says that the "geom" field of "unaprueba" table is required to be a geometry of a given type and you're trying to insert one of a different type instead. -- shows what type is expected by the table: \d unaprueba -- shows what you're trying to insert: SELECT ST_GeometryType( ST_ConvexHull(ST_Collect(geom)) FROM splitbeam_point ; ); --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] Postgis 1.5.3 regression test failures
On Thu, Oct 20, 2011 at 10:10:21AM +0200, Sandro Santilli wrote: > On Thu, Oct 20, 2011 at 10:27:29AM +1030, Stephen Davies wrote: > > I totally zapped the postgis source tree and started again from the tarball. > > > > Same result. > > > > However, this time I captured the PostgreSQL log. For the period of the > > make > > check run, the log is 5577 lines long but the first few lines are: > > > > LOG: database system is ready to accept connections > > LOG: autovacuum launcher started > > LOG: unexpected EOF on client connection > > LOG: unexpected EOF on client connection > > LOG: unexpected EOF on client connection > > LOG: unexpected EOF on client connection > > LOG: unexpected EOF on client connection > > LOG: unexpected EOF on client connection > > LOG: unexpected EOF on client connection > > LOG: unexpected EOF on client connection > > LOG: unexpected EOF on client connection > > LOG: unexpected EOF on client connection > > LOG: unexpected EOF on client connection > > LOG: unexpected EOF on client connection > > ERROR: parse error - invalid geometry at character 14 > > HINT: "POINT()" <-- parse error at position 7 within geometry > > STATEMENT: select '50', 'POINT()'::GEOMETRY as geom; > > ERROR: parse error - invalid geometry at character 14 > > HINT: "POINT(1)" <-- parse error at position 8 within geometry > > STATEMENT: select '51', 'POINT(1)'::GEOMETRY as geom; > > ERROR: parse error - invalid geometry at character 14 > > HINT: "POINT(," <-- parse error at position 7 within geometry > > STATEMENT: select '52', 'POINT(,)'::GEOMETRY as geom; > > ERROR: parse error - invalid geometry at character 14 > > > > Obviously I am missing something crucial but I cannot see what. > > A bug in the code producing those strings, obviously. Oops, I now see that's actually _expected_ as those bogus strings are intentionally passed to the parser to test error conditions :) So now I guess the problem is figuring out why regression test was failing. Were you not getting the ERROR strings in output ? --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] Postgis 1.5.3 regression test failures
On Thu, Oct 20, 2011 at 10:27:29AM +1030, Stephen Davies wrote: > I totally zapped the postgis source tree and started again from the tarball. > > Same result. > > However, this time I captured the PostgreSQL log. For the period of the make > check run, the log is 5577 lines long but the first few lines are: > > LOG: database system is ready to accept connections > LOG: autovacuum launcher started > LOG: unexpected EOF on client connection > LOG: unexpected EOF on client connection > LOG: unexpected EOF on client connection > LOG: unexpected EOF on client connection > LOG: unexpected EOF on client connection > LOG: unexpected EOF on client connection > LOG: unexpected EOF on client connection > LOG: unexpected EOF on client connection > LOG: unexpected EOF on client connection > LOG: unexpected EOF on client connection > LOG: unexpected EOF on client connection > LOG: unexpected EOF on client connection > ERROR: parse error - invalid geometry at character 14 > HINT: "POINT()" <-- parse error at position 7 within geometry > STATEMENT: select '50', 'POINT()'::GEOMETRY as geom; > ERROR: parse error - invalid geometry at character 14 > HINT: "POINT(1)" <-- parse error at position 8 within geometry > STATEMENT: select '51', 'POINT(1)'::GEOMETRY as geom; > ERROR: parse error - invalid geometry at character 14 > HINT: "POINT(," <-- parse error at position 7 within geometry > STATEMENT: select '52', 'POINT(,)'::GEOMETRY as geom; > ERROR: parse error - invalid geometry at character 14 > > Obviously I am missing something crucial but I cannot see what. A bug in the code producing those strings, obviously. --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
