[postgis-users] Convert MySql to Postgres
Hi, I have a MySql dump which is around 10 GB and I wish to convert it to Postgres and eventually PostGIS, but presently postgres since most of the data in it non-spatial. I am working on Ubuntu 12.04 with latest postgres installed. I am doing this for the first time and wanted to get this right. Could you provide me with a good resource or guide me so that I can do this successfully the first time? P.S. there are no procedures in the database so nothing to worry about the typecasting as of now. -- Thanks Regards Smaran Harihar ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Storing image file metadata in PostGIS
PostGIS has little or nothing to say about reading image metadata, but you'll find that you can get a long way by wrapping a bit of scripting around the output of 'gdalinfo' http://gdal.orgl or just using the gdal python bindings directly to read the metadata. Once you have extracted footprints, resolutions, and so on using GDAL, I'd suggest storing them as polygons in a 'geography' column in PostGIS. That will provide nice global indexing, no problems with datelines or poles, and a simple query model. P. On Sat, May 5, 2012 at 9:31 PM, Billy Newman newman...@gmail.com wrote: Hello all, I am currently using a pain old Oracle (not Oracle Spatial) database to store metadata for a huge amount of imagery data. I store this metadata for file discovery purposes. I.E. when a request is made for imagery I can quickly hit the DB to get all overlapping image files. I can then grab those off the file system and serve them out. I am using imageio-ext (Java) that sits on top of gdal to pull the imagery's metadata. Then just sending that to the DB. I am probably doing this the hard way and wondering if PostGIS can help simplify things. I am wondering if PostGIS has support to store metadata for images, and what that support really gives me. Currently I am serving out images in only EPSG:4326 so I want to do any transformations (if the image is not in EPSG:4326) to the metadata before storing it in the database. Can PostGIS read an image file and extract the metadata, transform the metadata into an EPSG:4326 lat/lon bounding box and store it? This is my current data model in Oracle if that helps: DataSource (this contains general info about this group of image files) ImageFile ( each data source has 0 - N image files) double minLat double maxLat double minLon double maxLon Its as simple as that. Although I am doing quite a bit of work using imageio-ext, and gdal to try and extract, transform, and store the metadata. Is this less complex in PostGIS or essentially the same? Thanks! Billy ___ 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 1.5.4 Released
Download from: http://postgis.org/download/postgis-1.5.4.tar.gz The news: This is a bug fix release, addressing issues that have been filed since the 1.5.3 release. - Bug Fixes - #547, ST_Contains memory problems (Sandro Santilli) - #621, Problem finding intersections with geography (Paul Ramsey) - #627, PostGIS/PostgreSQL process die on invalid geometry (Paul Ramsey) - #810, Increase accuracy of area calculation (Paul Ramsey) - #852, improve spatial predicates robustness (Sandro Santilli, Nicklas Avén) - #877, ST_Estimated_Extent returns NULL on empty tables (Sandro Santilli) - #1028, ST_AsSVG kills whole postgres server when fails (Paul Ramsey) - #1056, Fix boxes of arcs and circle stroking code (Paul Ramsey) - #1135, improve testsuite predictability (Andreas 'ads' Scherbaum) - #1146, images generator crashes (bronaugh) - #1170, North Pole intersection fails (Paul Ramsey) - #1179, ST_AsText crash with bad value (kjurka) - #1184, honour DESTDIR in documentation Makefile (Bryce L Nordgren) - #1227, server crash on invalid GML - #1252, SRID appearing in WKT (Paul Ramsey) - #1264, st_dwithin(g, g, 0) doesn't work (Paul Ramsey) - #1344, allow exporting tables with invalid geometries (Sandro Santilli) - #1389, wrong proj4text for SRID 31300 and 31370 (Paul Ramsey) - #1406, shp2pgsql crashes when loading into geography (Sandro Santilli) - #1595, fixed SRID redundancy in ST_Line_SubString (Sandro Santilli) - #1596, check SRID in UpdateGeometrySRID (Mike Toews, Sandro Santilli) - #1602, fix ST_Polygonize to retain Z (Sandro Santilli) - #1697, fix crash with EMPTY entries in GiST index (Paul Ramsey) - #1772, fix ST_Line_Locate_Point with collapsed input (Sandro Santilli) - #1799, Protect ST_Segmentize from max_length=0 (Sandro Santilli) - Alter parameter order in 900913 (Paul Ramsey) - Support builds with gmake (Greg Troxel) ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Storing image file metadata in PostGIS
Billy- FWIW, as you anyway have to give your data a loving hand you may consider the modular OGC standards suite. wrt data: - GML 3.2.1 Application Schema for Coverages (GMLCOV), OGC's general coverage model (including nD space/time rasters) in a general, flexible, encoding-independent manner, and harmonized across multiple OGC service standards (such as WCS and WPS) - Observations Measurements (OM) for modeling metadata alternatively (more flexibility, higher requirements on c/s) wrt services: - Web Catalog Service (CS-W) for generic metadata - Web Coverage Service (WCS) 2 (don't use older versions!) for serving coverages (including nD spatio-temporal rasters) FYI, OGC has an internally-ready candidate standard, WCS Application Profile for Coverages (EO-WCS), for integrated modeling + retrieval of Earth Observation data + metadata. If you wish I can send you material. Both are implemented by the FOSS combination of EOxServer (candidate reference implementation EO-WCS) + rasdaman (candidate reference implementation WCS, WCPS) + PostgreSQL + GDAL. cheers, Peter On 05/06/2012 01:02 AM, Greg Williamson wrote: Billy Newman wrote: Hello all, I am currently using a pain old Oracle (not Oracle Spatial) database to store metadata for a huge amount of imagery data. I store this metadata for file discovery purposes. I.E. when a request is made for imagery I can quickly hit the DB to get all overlapping image files. I can then grab those off the file system and serve them out. I am using imageio-ext (Java) that sits on top of gdal to pull the imagery's metadata. Then just sending that to the DB. I am probably doing this the hard way and wondering if PostGIS can help simplify things. I am wondering if PostGIS has support to store metadata for images, and what that support really gives me. Currently I am serving out images in only EPSG:4326 so I want to do any transformations (if the image is not in EPSG:4326) to the metadata before storing it in the database. Used to work with a postGIS database doing exactly this -- lat/lon coordinates, image type [b/w, etc/], date, some information about scale so queries for imagery in a given area was very fast to retrieve (the image files themselves were stored on disk). Can PostGIS read an image file and extract the metadata, transform the metadata into an EPSG:4326 lat/lon bounding box and store it? I am not familiar with all of the new raster data functionality in postGIS but at least earlier versions could not do this; GDAL perhaps can do this and then be used to load the data in postGIS. Not familiar with the imageio-ext tool you mention, sorry. Our DB started as Oracle (with an early spatial capability), migrated to Informix Spatial Blade, and then migrated to postgis so in and of itself this should not be a proble,, I had to do a little parsing of WKT to port from Informix to postGIS. This is my current data model in Oracle if that helps: DataSource (this contains general info about this group of image files) ImageFile ( each data source has 0 - N image files) double minLat double maxLat double minLon double maxLon Its as simple as that. Although I am doing quite a bit of work using imageio-ext, and gdal to try and extract, transform, and store the metadata. Is this less complex in PostGIS or essentially the same? It sounds as if you will still need to do some of the ETL work to get the data into postGIS in a consistent manner, but after that it should be easy to use postGIS as a data source. In general I have found postgresql to scale well although there can be issues tuning for specific usage patterns, etc. You don't give specific numbers but we had no issues with a database with millions of rows of spatial data. The postgres mailing lists (the general ones as well as this one) are very helpful and polite. Lots of developers available to answer specific questions. Not as easy to do that with Oracle, me thinks. Best of luck! Greg Williamson ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users -- Dr. Peter Baumann - Professor of Computer Science, Jacobs University Bremen www.faculty.jacobs-university.de/pbaumann mail: p.baum...@jacobs-university.de tel: +49-421-200-3178, fax: +49-421-200-493178 - Executive Director, rasdaman GmbH Bremen (HRB 26793) www.rasdaman.com, mail: baum...@rasdaman.com tel: 0800-rasdaman, fax: 0800-rasdafax, mobile: +49-173-5837882 Si forte in alienas manus oberraverit hec peregrina epistola incertis ventis dimissa, sed Deo commendata, precamur ut ei reddatur cui soli destinata, nec preripiat quisquam non sibi parata. (mail disclaimer, AD 1083) ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Updated PostGIS 2.0 Ubuntu 12.04 install instructions posted
At: http://trac.osgeo.org/postgis/wiki/UsersWikiPostGIS20Ubuntu1204 Please let me know where they go wrong!___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Do I have to be a superuser to use postgis extensions?
Using the GRANT sql worked. I read more about it in the postgresql manual. Thanks. GRANT USAGE ON SCHEMA topology TO PUBLIC; Then It never fully took effect till I restarted the server, *su -* *rcpostgresql restart* *exit* Done. Now, that part about giving access to PUBLIC on the template_postgis database. This is because without it, a non-superuser cannot create a database of their own when using: *createdb -T template_postgis newdb* *createdb: database creation failed: ERROR: permission denied to copy database template_postgis* I tried using the similar GRANT for databases but it doesn't mention anything about allowing users to copy. Only connect, so I tried that: *GRANT CONNECT ON DATABASE template_postgis TO PUBLIC;* But still, cannot create a database without being postgres user. Not sure if I need to maybe make user of ALTER like so: *ALTER DATABASE template_postgis OWNER TO PUBLIC;* Or try, *ALTER ROLE saultdon CREATEDB IN DATABASE template_postgis;* Does one of those give me copy permissions because CONNECT hasn't. Thanks for the help so far with changing schema privileges. Donovan On Sun, May 6, 2012 at 1:31 AM, Sandro Santilli s...@keybit.net wrote: On Sat, May 05, 2012 at 10:58:24PM -0700, Donovan Cameron wrote: When I to check the postgis version with the user saultdon, it says postgis_topology not installed. I have to be superuser to use it. ... *NOTICE: Function postgis_topology_scripts_installed() not found. Is topology support enabled and topology.sql installed?* Try GRANT usage on schema topology to public; Or variations of the above. When satisfied consider doing it on the template database. --strk; ,--o-. | __/ |Delivering high quality PostGIS 2.0 ! | / 2.0 |http://strk.keybit.net - http://vizzuality.com `-o--' ___ 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] Do I have to be a superuser to use postgis extensions?
I also read from some instructions for something unrelated [1] that this function also works, as postgres user: *su - postgres* *psql* *update pg_database set datistemplate = true where datname = 'template_postgis';* *\q* *exit* That looks like it is setting a configuration parameter in the pg_database listing that flags the template_postgis database as a template allowing it to be used in the same manner as template1. So I read further and found it on pg *1675* or* **Table 45-15. pg_database Columns *in the 9.1 Postgresql manual. This seems alot more appropriate than to use ALTER or GRANT which are irrelevant for what I am trying to do. So I tried it and it now allows non-postgres users to use template_postgis as a template in case any other openSUSE users need to know. But now the weird part... Any new databases I created from template_postgis doesn't have topology enabled again! But my first database does for some reason. The first database that is working was created from the postgres user that simply set the flag '-O saultdon' for the createdb command. The second database is created from the user saultdon from the template_postgis because I can copy it now. So I tried again to create the database as postgres: *postgres:~ createdb -O saultdon -T template_postgis newdb* * * Again, only postgres user can use topolgy schema and user saultdon cannot! I don't really understand what's happening because now the saultdon user can't use 'CREATE EXTENSION postgis;' on any databases created using just: createdb newdb where a template is not used. Gives me the same - must be superuser - error. This is even after I've run the 'GRANT USAGE ON SCHEMA topology TO PUBLIC;' And restarted the postgresql server. Donovan [1] http://code.grical.org/browser/INSTALL.TXT?rev=417%3A8ccbd40c0aba#L169 On Sun, May 6, 2012 at 11:30 AM, Donovan Cameron sault@gmail.comwrote: Using the GRANT sql worked. I read more about it in the postgresql manual. Thanks. GRANT USAGE ON SCHEMA topology TO PUBLIC; Then It never fully took effect till I restarted the server, *su -* *rcpostgresql restart* *exit* Done. Now, that part about giving access to PUBLIC on the template_postgis database. This is because without it, a non-superuser cannot create a database of their own when using: *createdb -T template_postgis newdb* *createdb: database creation failed: ERROR: permission denied to copy database template_postgis* I tried using the similar GRANT for databases but it doesn't mention anything about allowing users to copy. Only connect, so I tried that: *GRANT CONNECT ON DATABASE template_postgis TO PUBLIC;* But still, cannot create a database without being postgres user. Not sure if I need to maybe make user of ALTER like so: *ALTER DATABASE template_postgis OWNER TO PUBLIC;* Or try, *ALTER ROLE saultdon CREATEDB IN DATABASE template_postgis;* Does one of those give me copy permissions because CONNECT hasn't. Thanks for the help so far with changing schema privileges. Donovan On Sun, May 6, 2012 at 1:31 AM, Sandro Santilli s...@keybit.net wrote: On Sat, May 05, 2012 at 10:58:24PM -0700, Donovan Cameron wrote: When I to check the postgis version with the user saultdon, it says postgis_topology not installed. I have to be superuser to use it. ... *NOTICE: Function postgis_topology_scripts_installed() not found. Is topology support enabled and topology.sql installed?* Try GRANT usage on schema topology to public; Or variations of the above. When satisfied consider doing it on the template database. --strk; ,--o-. | __/ |Delivering high quality PostGIS 2.0 ! | / 2.0 |http://strk.keybit.net - http://vizzuality.com `-o--' ___ 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] Do I have to be a superuser to use postgis extensions?
Been playing further and found that the only way I can create a database for another user is not from the template_postgis because any user loses access to the topology schema for some reason but from a scratch database. This is even when I create the new database from the postgres user with: *createdb -T template_postgis -O saultdon newdb* *#user saultdon can not access topology schema* *psql newdb -c 'GRANT USAGE ON SCHEMA topology TO PUBLIC;'* *#user saultdon can not access topology schema* *dropdb newdb* Below is what works. As postgres user: *createdb newdb -O saultdon* *psql newdb -c 'CREATE EXTENSION postgis;'* *psql newdb -c 'CREATE EXTENSION postgis_topology;'* *psql newdb -c 'GRANT USAGE ON SCHEMA topology to PUBLIC;'* That is the only way I can get a postgis enabled database for a non-postgres user. Even though the GRANT USAGE has been used on template_postgis and my user saultdon can access the template_postgis and use 'SELECT postgis_full_version();' on it successfully, the user saultdon can create a new database from template_postgis but will lose access to the topology schema within the new db... Maybe this is a bug of some sort but not sure where to point it out. Or is there something I can suggest to the package maintainers for openSUSE to set some defaults like: 1.) create a template_postgis database by default a.) *GRANT USAGE ON SCHEMA topology TO PUBLIC;* and, b.) *update pg_database set datistemplate = true where datname = 'template_postgis';* * * Donovan On Sun, May 6, 2012 at 11:58 AM, Donovan Cameron sault@gmail.comwrote: I also read from some instructions for something unrelated [1] that this function also works, as postgres user: *su - postgres* *psql* *update pg_database set datistemplate = true where datname = 'template_postgis';* *\q* *exit* That looks like it is setting a configuration parameter in the pg_database listing that flags the template_postgis database as a template allowing it to be used in the same manner as template1. So I read further and found it on pg *1675* or* **Table 45-15. pg_database Columns *in the 9.1 Postgresql manual. This seems alot more appropriate than to use ALTER or GRANT which are irrelevant for what I am trying to do. So I tried it and it now allows non-postgres users to use template_postgis as a template in case any other openSUSE users need to know. But now the weird part... Any new databases I created from template_postgis doesn't have topology enabled again! But my first database does for some reason. The first database that is working was created from the postgres user that simply set the flag '-O saultdon' for the createdb command. The second database is created from the user saultdon from the template_postgis because I can copy it now. So I tried again to create the database as postgres: *postgres:~ createdb -O saultdon -T template_postgis newdb* * * Again, only postgres user can use topolgy schema and user saultdon cannot! I don't really understand what's happening because now the saultdon user can't use 'CREATE EXTENSION postgis;' on any databases created using just: createdb newdb where a template is not used. Gives me the same - must be superuser - error. This is even after I've run the 'GRANT USAGE ON SCHEMA topology TO PUBLIC;' And restarted the postgresql server. Donovan [1] http://code.grical.org/browser/INSTALL.TXT?rev=417%3A8ccbd40c0aba#L169 On Sun, May 6, 2012 at 11:30 AM, Donovan Cameron sault@gmail.comwrote: Using the GRANT sql worked. I read more about it in the postgresql manual. Thanks. GRANT USAGE ON SCHEMA topology TO PUBLIC; Then It never fully took effect till I restarted the server, *su -* *rcpostgresql restart* *exit* Done. Now, that part about giving access to PUBLIC on the template_postgis database. This is because without it, a non-superuser cannot create a database of their own when using: *createdb -T template_postgis newdb* *createdb: database creation failed: ERROR: permission denied to copy database template_postgis* I tried using the similar GRANT for databases but it doesn't mention anything about allowing users to copy. Only connect, so I tried that: *GRANT CONNECT ON DATABASE template_postgis TO PUBLIC;* But still, cannot create a database without being postgres user. Not sure if I need to maybe make user of ALTER like so: *ALTER DATABASE template_postgis OWNER TO PUBLIC;* Or try, *ALTER ROLE saultdon CREATEDB IN DATABASE template_postgis;* Does one of those give me copy permissions because CONNECT hasn't. Thanks for the help so far with changing schema privileges. Donovan On Sun, May 6, 2012 at 1:31 AM, Sandro Santilli s...@keybit.net wrote: On Sat, May 05, 2012 at 10:58:24PM -0700, Donovan Cameron wrote: When I to check the postgis version with the user saultdon, it says postgis_topology not installed. I have to be superuser to use it. ... *NOTICE: Function
Re: [postgis-users] Convert MySql to Postgres
On 6 May 2012 19:23, Smaran Harihar smaran.hari...@gmail.com wrote: Could you provide me with a good resource or guide me so that I can do this successfully the first time? I don't have any recent experience with this, but there is a pretty good list of free tools and scripts on the PostgreSQL wiki: http://wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreSQL#MySQL -Mike ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
