[postgis-users] restore database
Dear, i have made backup of spatial database and after i v restored it i couldn't visualize one geometry table in Qgis, uDIG,FME-viewer.Before i did backup of database i could visualize all data. I did backup and restore on same computer with PostgreSQL 8.3 installed. Database consists of 7 geometry tables, which 2 of geometry tables are loaded from shapefile, and one of those two cannot be visualized after backup and restore. So if anyone can help, thank you. -- Marko Čubranić ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] using dynamic tables in plpgsql nearest neighbour query
G'day all,
I am trying to sort out a routing problem over much of South-East Asia, and
trying to fill gaps using a number of different data sources.
I have a plpgsql function which works - with a static table name etc. When I
tried to change it to a dynamic name using the execute 'query' using variables;
statement, it doesn't work.
I am suspicious that the geometry type might be the cause
Anyways, this works : (using a multilinestring table with vertices assigned
using the pgrouting function.
CREATE OR REPLACE FUNCTION find_nearest_road(tabname varchar,
point geometry, sf varchar(6), OUT value int
) AS
$BODY$
DECLARE
max_search_radius real := 5.0; -- this is assuming working in degrees I
guess!
search_radius real := 0.01;
rec record; -- this has to match your lookup table
BEGIN
LOOP
SELECT gid, source, target INTO rec
FROM road m -- and you might want to change this
WHERE st_expand(point, search_radius) m.the_geom
ORDER BY st_distance(point, m.the_geom)
LIMIT 1;
IF FOUND THEN -- you could really simplify this, it might speed it up, but
I kind of like it.
IF substring(sf from 1 for 1) iLIKE 's' --start or Source
THEN
value := rec.source;
ELSIF substring(sf from 1 for 1) iLIKE 't' --target
OR substring(sf from 1 for 1) iLIKE 'f' --finish
THEN
value := rec.target;
ELSIF substring(sf from 1 for 1) iLIKE 'g' --gid
OR substring(sf from 1 for 1) iLIKE 'i' --id
THEN
value := rec.gid;
END IF;
EXIT;
END IF;
search_radius := search_radius * 2.0;
EXIT WHEN search_radius max_search_radius;
END LOOP;
END;
$BODY$ LANGUAGE plpgsql STABLE STRICT;
and this doesn't :- the first 5 lines after loop above have been replaced with
the 7 lines below, but I haven't even quoted the tablename - it's still
hardcoded.
CREATE OR REPLACE FUNCTION find_nearest_road(tabname varchar,
point geometry, sf varchar(6), OUT value int
) AS
$BODY$
DECLARE
max_search_radius real := 5.0; -- this is assuming working in degrees I
guess!
search_radius real := 0.01;
rec record; -- this has to match your lookup table
BEGIN
LOOP
EXECUTE 'SELECT gid, source, target
FROM road m -- and you might want to change this
WHERE st_expand($1, $2) m.the_geom
ORDER BY st_distance($1, m.the_geom)
LIMIT 1'
INTO rec
USING point, search_radius;
IF FOUND THEN -- you could really simplify this, it might speed it up, but
I kind of like it.
IF substring(sf from 1 for 1) iLIKE 's' --start or Source
THEN
value := rec.source;
ELSIF substring(sf from 1 for 1) iLIKE 't' --target
OR substring(sf from 1 for 1) iLIKE 'f' --finish
THEN
value := rec.target;
ELSIF substring(sf from 1 for 1) iLIKE 'g' --gid
OR substring(sf from 1 for 1) iLIKE 'i' --id
THEN
value := rec.gid;
END IF;
EXIT;
END IF;
search_radius := search_radius * 2.0;
EXIT WHEN search_radius max_search_radius;
END LOOP;
END;
$BODY$ LANGUAGE plpgsql VOLATILE STRICT;
the first function returns :
prices=# select
find_nearest_road('road','010120E61052FC3DCF94A459409734BBCFC2243240'::geometry,'s');
find_nearest_road
---
1507
(1 row)
and the second version of the function :
prices=# select
find_nearest_road('road','010120E61052FC3DCF94A459409734BBCFC2243240'::geometry,'s');
find_nearest_road
---
(1 row)
So I guess it comes down to what's wrong between :
SELECT gid, source, target INTO rec FROM road m WHERE st_expand(point,
search_radius) m.the_geom ORDER BY st_distance(point, m.the_geom) LIMIT 1;
EXECUTE
'SELECT gid, source, target FROM road m WHERE st_expand($1, $2) m.the_geom
ORDER BY st_distance($1, m.the_geom) LIMIT 1'
INTO rec USING point, search_radius;
Any advice gratefully received,
Ben
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] ANN: zigGIS to be re-open sourced
The next full version release of zigGIS (3.0) will return to an OSI approved open source license. We think this is the right move to return the power back to the GIS community and more closely track PostGIS. Details can be found here: http://abegillespie.blogspot.com/2010/04/prodigal-extension.html and here: http://groups.google.com/group/ziggis/browse_thread/thread/a1f19e54c75ddd32 -Abe ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] ANN: zigGIS to be re-open sourced
That's great news! George On Fri, Apr 23, 2010 at 2:30 PM, Abram Gillespie abe.gillespie.li...@gmail.com wrote: The next full version release of zigGIS (3.0) will return to an OSI approved open source license. We think this is the right move to return the power back to the GIS community and more closely track PostGIS. Details can be found here: http://abegillespie.blogspot.com/2010/04/prodigal-extension.html and here: http://groups.google.com/group/ziggis/browse_thread/thread/a1f19e54c75ddd32 -Abe ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users -- George R. C. Silva Desenvolvimento em GIS http://blog.geoprocessamento.net ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Polygon length zero
Hi,
using this polygon:
MULTIPOLYGON(((1 1, 2 3, 10 10, 1 1)))
The Length return is zero.
Perhaps I'm forget something ?
This is my simply sql code.
create table _uno (id serial primary key);
SELECT AddGeometryColumn('_uno', 'geom', 3003, 'MULTIPOLYGON', 2);
insert into _uno (geom) values (ST_GeomFromText('MULTIPOLYGON(((1 1, 2
3, 10 10, 1 1)))',3003));
select st_length(geom) from _uno
---
0
I'm using Postgres 8.4.3 - Postgis 1.5.1 on windows 7 - 64 bit.
Thx,
Andrea Peri.
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Polygon length zero
Try ST_Perimeter() for areal geometries.
Fred
On Fri, Apr 23, 2010 at 6:45 PM, Andrea Peri 2007 aperi2...@gmail.comwrote:
Hi,
using this polygon:
MULTIPOLYGON(((1 1, 2 3, 10 10, 1 1)))
The Length return is zero.
Perhaps I'm forget something ?
This is my simply sql code.
create table _uno (id serial primary key);
SELECT AddGeometryColumn('_uno', 'geom', 3003, 'MULTIPOLYGON', 2);
insert into _uno (geom) values (ST_GeomFromText('MULTIPOLYGON(((1 1, 2 3,
10 10, 1 1)))',3003));
select st_length(geom) from _uno
---
0
I'm using Postgres 8.4.3 - Postgis 1.5.1 on windows 7 - 64 bit.
Thx,
Andrea Peri.
___
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] Bug in postgis installation on WinServe r2008-64bitþ
John, We are trying to figure out what the specific issue is here. We haven't as of yet had trouble installing on Windows 2008, though haven't tried Windows 2008 R2. Are you running regular Windows 2008 or Windows 2008 R2? Also it is possible we aren't properly escaping special characters, can you verify that the password you thought you typed in is the one for the postgres PostgreSQL super user account, and not the postgres windows account? Although both users go by the same name -- the PostgreSQL account system is detached from the windows account system, so although they have the same name, they might not have the same password. This has caused quite a lot of confusion for people particularly people new to PostgreSQL. We are also a bit puzzled about the setting pg_hba.conf to trust and it not working. In this setting any password you type even if it is wrong would work. Did you reload the config or restart the service? The settings don't take effect unless you reload the config with a postgresql reload or restart the service. We'll experiment on our end to see if the % in password is causing a problem. Thanks, Leo and Regina http://www.postgis.us _ From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of john lace Sent: Thursday, April 22, 2010 10:39 PM To: postgis-users@postgis.refractions.net Subject: [postgis-users] Bug in postgis installation on WinServer2008-64bit Hi, I just found a problem with the Postgis 1.5 installation on a Windows Server 2008 64 bit platform, When I installed postgres, it requested a more secure password for user 'postgres', maybe because this is a policy on WinServer2008, and I used a combination of special chars, something like 'my00%$password' Postgres accepted it very well, but the installation of postgis didn't. Not postgis 1.4, nor 1.5, not on postgres 8.3, 8.3.7, 8.4.3. I kept installing different options and watching them fail. The postgis installation kept failing, with an error 'password is incorrect for user postgres', even when I edited pg_hba.conf to make it trust 127.0.0.1 The problem got worse, because when the postgis installation failed, some process was left behind, and next installation could fail on a different part of the installation. Workaround? I added another postgres user, as a superuser, granted everything. I didn't give this user special chars in the password, and I installed postgis with this user. It worked flawlesly. End of the problem. I'm writing just to let you know, in case it helps someone else or someone can upload it to the bug list to get corrected. If a password is accepted on postgres, should be accepted on a postgis installation. Best regards. John Lace _ ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] restore database
Marko, Not sure off hand what could be wrong here. Did you verify the table you can't visualize has data? Its possible the data didn't load or didn't get backed up because it had a very invalid geometry in it like a polygon with two few points. This issue was fixed in PostGIS 1.4.2 and PostGIS 1.5.1. Before it was possible that you could create a record in PostGIS that was clearly invalid and you would no longer be able to export it out or reimport it. We are guessing that might be the issue here. Leo and Regina http://www.postgis.us _ From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Marko Cubranic Sent: Friday, April 23, 2010 3:27 AM To: postgis-users@postgis.refractions.net Subject: [postgis-users] restore database Dear, i have made backup of spatial database and after i v restored it i couldn't visualize one geometry table in Qgis, uDIG,FME-viewer.Before i did backup of database i could visualize all data. I did backup and restore on same computer with PostgreSQL 8.3 installed. Database consists of 7 geometry tables, which 2 of geometry tables are loaded from shapefile, and one of those two cannot be visualized after backup and restore. So if anyone can help, thank you. -- Marko Čubranić ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] [postgis-devel] St_Intersection on same table
Rafal, This question is more appropriate for the PostGIS User's group rather than the PostGIS developer's group. To answer your question -- We are suspecting you will want to do a SELF JOIN accompanied by a ST_Union, ST_Intersection, and/or possibly a ST_SymDifference. What is unclear to us is If you have 3 polygons - lets say 1,2,3 1 intersects with 2 2 intersects with 3 (but not with 1) What are you expecting to happen here? A) intersection(1,2) count 2, Intersection(2,3) count 2 Or B) You don't allow the same geometry to be counted twice in which case You throw out the whole thing return the empty set Or C) You arbitrarily pick a set and return intersection (1,2) D) Similarly we assume if you have a case where 1,2,3 all intersect then you would return ST_Intersection(ST_Intersection(1,2),3), count 3 ? Or Would you treat as a pairwise intersection E) 1,2,3 x 1,2, 3 intesection resutling in 3 records with count of 2 each 1,2 2,3 1,3 Leo and Regina http://www.postgis.us -Original Message- From: postgis-devel-boun...@postgis.refractions.net [mailto:postgis-devel-boun...@postgis.refractions.net] On Behalf Of Rafal Foltynski Sent: Friday, April 23, 2010 11:32 AM To: postgis-de...@postgis.refractions.net Subject: [postgis-devel] St_Intersection on same table Hi, I have a table with many overlying polygons. I want to create a new polygon relation from the intersection of these polygons that has two fields 1. geometry of small polygons - the result of St_Intersection() and 2. count of the number of overlying polygons that each small polygon was created from. How do I create a query to run St_Intersect on the same geometry field in one table? For example: if I have a table with 3 polygons, 2 of them share a small area and the third polygon is disjoint, the resulting polygon relation would only have one small polygon area (shared area between two polygons) and count of 2. Rafal ___ postgis-devel mailing list postgis-de...@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-devel ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] [postgis-devel] St_Intersection on same table
From: postgis-devel-boun...@postgis.refractions.net [mailto:postgis-devel-boun...@postgis.refractions.net] On Behalf Of Rafal Foltynski Sent: Friday, April 23, 2010 11:32 AM To: postgis-de...@postgis.refractions.net Subject: [postgis-devel] St_Intersection on same table Hi, I have a table with many overlying polygons. I want to create a new polygon relation from the intersection of these polygons that has two fields 1. geometry of small polygons - the result of St_Intersection() and 2. count of the number of overlying polygons that each small polygon was created from. How do I create a query to run St_Intersect on the same geometry field in one table? For example: if I have a table with 3 polygons, 2 of them share a small area and the third polygon is disjoint, the resulting polygon relation would only have one small polygon area (shared area between two polygons) and count of 2. Rafal My query for this would look something like: select count(distinct g1.gid), count(distinct g2.gid), array_accum(distinct g1.gid), array_accum(distinct g2.gid), st_collect(distinct g1.geometry) as st_collect1, st_collect(distinct g2.geometry) as st_collect2, st_intersection(g1.geometry, g2.geometry), st_area(st_intersection(g1.geometry, g2.geometry)) from geom_table g1, geom_table g2 where g1.gid g2.gid and st_intersects(g1.geometry, g2.geometry) and st_isvalid(g1.geometry) and st_isvalid(g2.geometry) group by st_intersection(g1.geometry, g2.geometry) order by count(distinct g1.gid), st_area(st_intersection(g1.geometry, g2.geometry)) A few notes: - I use a custom array_accum function described here: http://www.postgresql.org/docs/current/static/xaggr.html - My primary key is gid serial, which is used in the array_accum aggregate function for my information - There might be a few cases where count(distinct g1.gid) count(distinct g2.gid), so you might want both counts shown. This seems to be the case if you have multiple exact geometries with different PKs. Hope this helps. -Mike ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Build errors in MAC OSX
I am trying to build the PostGIS 1.5 files in Mac OSX and I am getting the following error: make -C liblwgeom gcc -g -O2 -fno-common -DPIC -Wall -Wmissing-prototypes -c -o measures.o measures.c /var/tmp//ccyiv2X6.s:unknown:FATAL:can't create output file: measures.o make[1]: *** [measures.o] Error 1 make: *** [liblwgeom] Error 2 Any thoughts on how to deal with this? Thanks, John___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Build errors in MAC OSX
What happens when you run 'touch test'? P On Fri, Apr 23, 2010 at 5:33 PM, John Connors jconn...@berkeley.edu wrote: I am trying to build the PostGIS 1.5 files in Mac OSX and I am getting the following error: make -C liblwgeom gcc -g -O2 -fno-common -DPIC -Wall -Wmissing-prototypes -c -o measures.o measures.c /var/tmp//ccyiv2X6.s:unknown:FATAL:can't create output file: measures.o make[1]: *** [measures.o] Error 1 make: *** [liblwgeom] Error 2 Any thoughts on how to deal with this? Thanks, John ___ 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] Bug in postgis installation on WinServe r2008-64bitþ
Thanks for your reply. Here are the details you request. Yes, the password was the same for both 'postgres' accounts, even when the same postgres install recommends not to, I always do that to avoid the confusion you mention. The pg_hab.conf also confused me, but yes, I restarted the apache server several times, lots of times, I even restarted the whole computer 3 times. And going with pgAdmin before installing postgis, I could sign into the database without a password, or with any password. So yes, this is what it was doing even when pg_hba.conf said 'trust', I'm positive. Yes, the % could be the problem. Even when the password I used was not exactly what I wrote here, it has the same non-alphabethic chars I included in my original post.$ % .A escape problem might be the very first thing to try, please check these 3 chars first. I'm not really sure if it was winserver2008 R2 or not, and now I can't confirm that. Sorry. But it is 64 bits.And the machine is brand new, less than a month old. Maybe you haven't had any problem because you have never used the offending char(s) in your passwords? By the way, I have installed postgis on CentOS, winXP SP2, SP3, winServer2003, win7 64 bits, and now server2008-64, and this is the first problem I have really encountered with it. Good job. Hope you find the problem quickly. John Lace From: l...@pcorp.us To: postgis-users@postgis.refractions.net Date: Fri, 23 Apr 2010 15:30:24 -0400 Subject: Re: [postgis-users] Bug in postgis installation on WinServer2008-64bitþ John, We are trying to figure out what the specific issue is here. We haven't as of yet had trouble installing on Windows 2008, though haven't tried Windows 2008 R2. Are you running regular Windows 2008 or Windows 2008 R2? Also it is possible we aren't properly escaping special characters, can you verify that the password you thought you typed in is the one for the postgres PostgreSQL super user account, and not the postgres windows account? Although both users go by the same name -- the PostgreSQL account system is detached from the windows account system, so although they have the same name, they might not have the same password. This has caused quite a lot of confusion for people particularly people new to PostgreSQL. We are also a bit puzzled about the setting pg_hba.conf to trust and it not working. In this setting any password you type even if it is wrong would work. Did you reload the config or restart the service? The settings don't take effect unless you reload the config with a postgresql reload or restart the service. We'll experiment on our end to see if the % in password is causing a problem. Thanks, Leo and Regina http://www.postgis.us From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of john lace Sent: Thursday, April 22, 2010 10:39 PM To: postgis-users@postgis.refractions.net Subject: [postgis-users] Bug in postgis installation on WinServer2008-64bit Hi, I just found a problem with the Postgis 1.5 installation on a Windows Server 2008 64 bit platform, When I installed postgres, it requested a more secure password for user 'postgres', maybe because this is a policy on WinServer2008, and I used a combination of special chars, something like 'my00%$password' Postgres accepted it very well, but the installation of postgis didn't. Not postgis 1.4, nor 1.5, not on postgres 8.3, 8.3.7, 8.4.3. I kept installing different options and watching them fail. The postgis installation kept failing, with an error 'password is incorrect for user postgres', even when I edited pg_hba.conf to make it trust 127.0.0.1 The problem got worse, because when the postgis installation failed, some process was left behind, and next installation could fail on a different part of the installation. Workaround? I added another postgres user, as a superuser, granted everything. I didn't give this user special chars in the password, and I installed postgis with this user. It worked flawlesly. End of the problem. I'm writing just to let you know, in case it helps someone else or someone can upload it to the bug list to get corrected. If a password is accepted on postgres, should be accepted on a postgis installation. Best regards. John Lace _ ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
