Re: [postgis-users] Help dumping data from an old database
Here is a thought. Move all the tables without geometry columns to a new
schema and then backup that new schema.
So this script for example will generate logic to move all the tables in
public that do not contain geometry_columns.
CREATE SCHEMA nongeomdata;
SELECT 'ALTER TABLE ' || table_name || ' SET SCHEMA nongeomdata;'
FROM information_schema.tables
WHERE table_schema = 'public' AND table_name NOT
IN('geometry_columns', 'spatial_ref_sys')
AND table_name NOT IN(SELECT table_name FROM
information_schema.columns
WHERE udt_name = 'geometry' AND
table_schema = 'public');
Then simple matter of backing up the new schema
pg_dump --schema=nongeomdata -F c -b -v -f "/somepath/somedb.backup" somedb
You would do the reverse the set schema back to public or whatever when you
restore if you want.
Hope that helps,
Leo and Regina
http://www.postgis.us
Re: [postgis-users] Help dumping data from an old database
Chris, This is probably a bad suggestion, but iff you are desparate and iff you can build a more recent postgis, then to dump it out you may find that creating a simlink from a more recent liblwgeom.so with the name of the missing one might work. the COPY command won't actually take anything other than the SQL, so shouldn't affect the importing. cheers Ben On 17/08/2011, at 6:28 AM, Chris Gat wrote: > Hello, > > I've recently been tasked with reviving an older database that hasn't been > touch in a while. Both postgres and postgis were used to create this > database. My ultimate goal at this point is to be able to dump the database > from the linux server where it currently resides, to a mac workstation. > > Here is the short version of what I've done so far and the problem I'm having: > build/install postgresql 8.2.3 > at this point, I can access the database (via psql, SELECT), but if I try to > use pg_dump (pg_dump MYDB > MYDB.sql), I get the error: > > ERROR: could not access file "$libdir/liblwgeom.1.2.so": No such file or > directory > STATEMENT: COPY public.boundaries (gid, fnode_, tnode_, lpoly_, rpoly_, > length, world_, world_id, bnd_type, bnd_status, bnd_study, area, len, > the_geom) TO stdout; > > Obviously, after some investigation, I realize I need postgis. So I, > build/install proj4 4.7 > build/install geos 3.1.1 > when I try to build postgis-1.2.1, I get the build error (after running make): > > lwgeom_geos_c.c: In function `postgis_geos_version': > lwgeom_geos_c.c:84: warning: implicit declaration of function `VARATT_SIZEP' > lwgeom_geos_c.c:84: error: invalid lvalue in assignment > lwgeom_geos_c.c: In function `relate_full': > lwgeom_geos_c.c:2073: error: invalid lvalue in assignment > lwgeom_geos_c.c: In function `GEOS2LWGEOM': > lwgeom_geos_c.c:2430: warning: assignment discards qualifiers from pointer > target type > lwgeom_geos_c.c:2439: warning: assignment discards qualifiers from pointer > target type > lwgeom_geos_c.c:2449: warning: assignment discards qualifiers from pointer > target type > lwgeom_geos_c.c:2450: warning: assignment discards qualifiers from pointer > target type > lwgeom_geos_c.c:2454: warning: assignment discards qualifiers from pointer > target type > lwgeom_geos_c.c:2455: warning: assignment discards qualifiers from pointer > target type > lwgeom_geos_c.c:2476: warning: assignment discards qualifiers from pointer > target type > lwgeom_geos_c.c: In function `polygonize_garray': > lwgeom_geos_c.c:2815: warning: passing arg 1 of `GEOSPolygonize' from > incompatible pointer type > lwgeom_geos_c.c: In function `LWGEOM_buildarea': > lwgeom_geos_c.c:2965: warning: passing arg 1 of `GEOSPolygonize' from > incompatible pointer type > lwgeom_geos_c.c:3008: warning: assignment discards qualifiers from pointer > target type > > I can build/install postgis-1.3 and postgis-1.4, but these versions don't > create the proper liblwgeom.so file. > > Any ideas as to why this is occurring would help greatly. I should also > mention that the most important information in this database has nothing to > do with postgis, therefore, if there is a way to ignore the tables associated > with postgis, and thus avoid the liblwgeom requirement, that would suffice. > > Some additional information: > -when configuring postgres, proj4, geos, I used --prefix=/mydirectory/ > -when configuring postgis, I used --prefix=/mydirectory/ > --with-psql=/pathToPgconfig --with-geos=/pathToGeoConfig > --with-proj=/mydirectory/ > > Also, I use GNU make 3.8 > > Thanks for you help, I appreciate it. > > Chris > > ___ > 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] Help dumping data from an old database
Chris, just use the postgres copy command to copy out the data you need, and then copy into your new DB then. It can take an arbitrary sql expression so if needed do a query that grabs the columns you care about and ignores the location ones. hth charles On Aug 16, 2011, at 6:28 PM, Chris Gat wrote: > Hello, > > I've recently been tasked with reviving an older database that hasn't been > touch in a while. Both postgres and postgis were used to create this > database. My ultimate goal at this point is to be able to dump the database > from the linux server where it currently resides, to a mac workstation. > > Here is the short version of what I've done so far and the problem I'm having: > build/install postgresql 8.2.3 > at this point, I can access the database (via psql, SELECT), but if I try to > use pg_dump (pg_dump MYDB > MYDB.sql), I get the error: > > ERROR: could not access file "$libdir/liblwgeom.1.2.so": No such file or > directory > STATEMENT: COPY public.boundaries (gid, fnode_, tnode_, lpoly_, rpoly_, > length, world_, world_id, bnd_type, bnd_status, bnd_study, area, len, > the_geom) TO stdout; > > Obviously, after some investigation, I realize I need postgis. So I, > build/install proj4 4.7 > build/install geos 3.1.1 > when I try to build postgis-1.2.1, I get the build error (after running make): > > lwgeom_geos_c.c: In function `postgis_geos_version': > lwgeom_geos_c.c:84: warning: implicit declaration of function `VARATT_SIZEP' > lwgeom_geos_c.c:84: error: invalid lvalue in assignment > lwgeom_geos_c.c: In function `relate_full': > lwgeom_geos_c.c:2073: error: invalid lvalue in assignment > lwgeom_geos_c.c: In function `GEOS2LWGEOM': > lwgeom_geos_c.c:2430: warning: assignment discards qualifiers from pointer > target type > lwgeom_geos_c.c:2439: warning: assignment discards qualifiers from pointer > target type > lwgeom_geos_c.c:2449: warning: assignment discards qualifiers from pointer > target type > lwgeom_geos_c.c:2450: warning: assignment discards qualifiers from pointer > target type > lwgeom_geos_c.c:2454: warning: assignment discards qualifiers from pointer > target type > lwgeom_geos_c.c:2455: warning: assignment discards qualifiers from pointer > target type > lwgeom_geos_c.c:2476: warning: assignment discards qualifiers from pointer > target type > lwgeom_geos_c.c: In function `polygonize_garray': > lwgeom_geos_c.c:2815: warning: passing arg 1 of `GEOSPolygonize' from > incompatible pointer type > lwgeom_geos_c.c: In function `LWGEOM_buildarea': > lwgeom_geos_c.c:2965: warning: passing arg 1 of `GEOSPolygonize' from > incompatible pointer type > lwgeom_geos_c.c:3008: warning: assignment discards qualifiers from pointer > target type > > I can build/install postgis-1.3 and postgis-1.4, but these versions don't > create the proper liblwgeom.so file. > > Any ideas as to why this is occurring would help greatly. I should also > mention that the most important information in this database has nothing to > do with postgis, therefore, if there is a way to ignore the tables associated > with postgis, and thus avoid the liblwgeom requirement, that would suffice. > > Some additional information: > -when configuring postgres, proj4, geos, I used --prefix=/mydirectory/ > -when configuring postgis, I used --prefix=/mydirectory/ > --with-psql=/pathToPgconfig --with-geos=/pathToGeoConfig > --with-proj=/mydirectory/ > > Also, I use GNU make 3.8 > > Thanks for you help, I appreciate it. > > Chris > > ___ > 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] Help dumping data from an old database
Hello, I've recently been tasked with reviving an older database that hasn't been touch in a while. Both postgres and postgis were used to create this database. My ultimate goal at this point is to be able to dump the database from the linux server where it currently resides, to a mac workstation. Here is the short version of what I've done so far and the problem I'm having: build/install postgresql 8.2.3 at this point, I can access the database (via psql, SELECT), but if I try to use pg_dump (pg_dump MYDB > MYDB.sql), I get the error: ERROR: could not access file "$libdir/liblwgeom.1.2.so": No such file or directory STATEMENT: COPY public.boundaries (gid, fnode_, tnode_, lpoly_, rpoly_, length, world_, world_id, bnd_type, bnd_status, bnd_study, area, len, the_geom) TO stdout; Obviously, after some investigation, I realize I need postgis. So I, build/install proj4 4.7 build/install geos 3.1.1 when I try to build postgis-1.2.1, I get the build error (after running make): lwgeom_geos_c.c: In function `postgis_geos_version': lwgeom_geos_c.c:84: warning: implicit declaration of function `VARATT_SIZEP' lwgeom_geos_c.c:84: error: invalid lvalue in assignment lwgeom_geos_c.c: In function `relate_full': lwgeom_geos_c.c:2073: error: invalid lvalue in assignment lwgeom_geos_c.c: In function `GEOS2LWGEOM': lwgeom_geos_c.c:2430: warning: assignment discards qualifiers from pointer target type lwgeom_geos_c.c:2439: warning: assignment discards qualifiers from pointer target type lwgeom_geos_c.c:2449: warning: assignment discards qualifiers from pointer target type lwgeom_geos_c.c:2450: warning: assignment discards qualifiers from pointer target type lwgeom_geos_c.c:2454: warning: assignment discards qualifiers from pointer target type lwgeom_geos_c.c:2455: warning: assignment discards qualifiers from pointer target type lwgeom_geos_c.c:2476: warning: assignment discards qualifiers from pointer target type lwgeom_geos_c.c: In function `polygonize_garray': lwgeom_geos_c.c:2815: warning: passing arg 1 of `GEOSPolygonize' from incompatible pointer type lwgeom_geos_c.c: In function `LWGEOM_buildarea': lwgeom_geos_c.c:2965: warning: passing arg 1 of `GEOSPolygonize' from incompatible pointer type lwgeom_geos_c.c:3008: warning: assignment discards qualifiers from pointer target type I can build/install postgis-1.3 and postgis-1.4, but these versions don't create the proper liblwgeom.so file. Any ideas as to why this is occurring would help greatly. I should also mention that the most important information in this database has nothing to do with postgis, therefore, if there is a way to ignore the tables associated with postgis, and thus avoid the liblwgeom requirement, that would suffice. Some additional information: -when configuring postgres, proj4, geos, I used --prefix=/mydirectory/ -when configuring postgis, I used --prefix=/mydirectory/ --with-psql=/pathToPgconfig --with-geos=/pathToGeoConfig --with-proj=/mydirectory/ Also, I use GNU make 3.8 Thanks for you help, I appreciate it. Chris ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] st_geometryn
Hi When using st_geometryn does it always count in the same direction(+ to –)?? Bob___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] PostGIS to Web Map?
James, You could have a look at GeoMoose ; it supplies a couple of demo configurations to get people started. It uses OpenLayers and Mapserver, and can use PostGIS as a datastore for it's web-based feature editor. Best Regards, Brent Fraser On 8/16/2011 12:11 PM, James David Smith wrote: Dear all, Bit of a geneal question this rather than something specific, but I've been following a few of the other queries on here talking about Open Layers, Geoserver and Google Maps, and it's reminded me that I really want to try and learn how to connect/convert my local PostGIS database into something that renders on a webpage. I realise that this is a big question, but wondered if anyone could give me a basic few steps as way of pointers - and then perhaps some links to more detail where I can go and learn how to do each step myself? I'm very happy to learn - just not sure where to start! To be clear, at the moment I have a local database on my machine. I guess really I need to move this database to an online environment and host it somewhere? Then I need to build some webpages with a map, that interact with the database? There's no specific project here, just something I want to learn in my spare time as it'll be useful for the future. As you can see my knowledge of how to put all this together is a bit vague to say the least. If it helps I have basic experience of Java + Javescript including the Google Maps API, am decent at HTML and CSS, and am becoming familiar with PostgreSQL and PostGIS - I just don't know how to join them all up at the moment - or where the gaps in my knowledge are. Best wishes James ___ 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] PostGIS to Web Map?
Try the OpenGeo Suite, shortest distance between two points... http://opengeo.org/products/suite/ P. On Tue, Aug 16, 2011 at 11:11 AM, James David Smith wrote: > Dear all, > > Bit of a geneal question this rather than something specific, but I've > been following a few of the other queries on here talking about Open > Layers, Geoserver and Google Maps, and it's reminded me that I really > want to try and learn how to connect/convert my local PostGIS database > into something that renders on a webpage. I realise that this is a big > question, but wondered if anyone could give me a basic few steps as > way of pointers - and then perhaps some links to more detail where I > can go and learn how to do each step myself? I'm very happy to learn - > just not sure where to start! > > To be clear, at the moment I have a local database on my machine. I > guess really I need to move this database to an online environment and > host it somewhere? Then I need to build some webpages with a map, that > interact with the database? There's no specific project here, just > something I want to learn in my spare time as it'll be useful for the > future. > > As you can see my knowledge of how to put all this together is a bit > vague to say the least. If it helps I have basic experience of Java + > Javescript including the Google Maps API, am decent at HTML and CSS, > and am becoming familiar with PostgreSQL and PostGIS - I just don't > know how to join them all up at the moment - or where the gaps in my > knowledge are. > > Best wishes > > James > ___ > 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] PostGIS to Web Map?
Dear all, Bit of a geneal question this rather than something specific, but I've been following a few of the other queries on here talking about Open Layers, Geoserver and Google Maps, and it's reminded me that I really want to try and learn how to connect/convert my local PostGIS database into something that renders on a webpage. I realise that this is a big question, but wondered if anyone could give me a basic few steps as way of pointers - and then perhaps some links to more detail where I can go and learn how to do each step myself? I'm very happy to learn - just not sure where to start! To be clear, at the moment I have a local database on my machine. I guess really I need to move this database to an online environment and host it somewhere? Then I need to build some webpages with a map, that interact with the database? There's no specific project here, just something I want to learn in my spare time as it'll be useful for the future. As you can see my knowledge of how to put all this together is a bit vague to say the least. If it helps I have basic experience of Java + Javescript including the Google Maps API, am decent at HTML and CSS, and am becoming familiar with PostgreSQL and PostGIS - I just don't know how to join them all up at the moment - or where the gaps in my knowledge are. Best wishes James ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Tiger Geocoder return values
Hi all,
Using the tiger geocoder, I basically want to update a table with addresses to
also include the geocoded points. While the example in the docs definitely
works, it is a bit convoluted and tricky. So here is my first question: Does
anyone have a simpler pattern they follow when updating a table with geocodes
than the multiple CTE's and subselects and aliasing used in the example?
I have also been thinking about a couple of different approaches and wanted to
know if anyone thought they would be useful:
1. How about a polymorphic version of geocode() that returns a "geocode" type
basically defined as {norm_addy, rating, geomout}? Then one could have a column
in a table, run an update statement like
create table mytab (addr text, mygeocode geocode, mypoint geom);
UPDATE mytab set mygeocode=geocode(addr, 1);
UPDATE mytab set mypoint=mygeocode.geomout where mygeocode.rating<10;
I could write such a thing and submit a patch -- it would be a new couple of
functions and a type definition I think.
2. Alternatively, one might also write a polymorphic version which allows one
to pass through an ID that is given a column in the return table; one could use
this to join directly after creating the geocode table.
Any thoughts? Would this be useful? Any improvements to the above ideas that
might be nice? I might not be thinking through the problem of returning
multiple rows and types enough
And Regina -- I will send you my name once I dig up your email. We have
corresponded before. I have nothing particularly interesting to hide, I just
hate to see my stupid questions all attached to my real name ;).
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
