Re: [postgis-users] Projection problem
Hi Ralf. You gave me the correct way. Thanks a lot. Finally, I used a similar SQL, with the double transformation you said, and result is good: CREATE OR REPLACE VIEW vista_23030 AS SELECT GID, transform_geometry(transform( geom, 4326), '+proj=longlat +ellps=WGS84 +datum=WGS84 +no_defs '::text, '+proj=utm +ellps=intl +zone=30 +units=m +towgs84=-131,-100.3,-163.4,-1.244,-0.02,-1.14 +no_defs'::text, 23030) AS geom FROM capa_25830; Regards Jaime 2010/7/23 Ralf Suhr ralf.s...@itc-halle.de Hi Jaime, to get transformation with towgs84 parameter working you have to use wgs84 ellipsoid. Transform from ETRS89 to wgs84 and second from wgs84 to ED50. ST_Transform(ST_Transform( 25830_geom, 4326), 23030) Gr Ralf Am Freitag 23 Juli 2010, 08:42:39 schrieb Jaime Fiers: Hello guys I hava a little problem with Postgis projection. I need get a reprojected view. Original data layer is in SRS=25830 (ETRS89 UTM30) and view in SRS=23030 (ED50 UTM30) I try this: CREATE OR REPLACE VIEW vista_23030 AS SELECT id, transform_geometry(GEOMETRY, '+proj=utm +zone=30 +ellps=GRS80 +units=m +no_defs +towgs84=131.0,100.3,163.4,1.24399989,0.01998,1.143 99988,-9.39 '::text, '+proj=utm +zone=30 +ellps=intl +units=m +nadgrids +no_defs'::text, 23030) AS GEOMETRY FROM capa_25830; (If we do not include +nadgrids we don't get anything, but that is not a problem). I always get a +-90 meter error. Can anybody help me. Thanks in advance ___ 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] JASPA (JAva SPATial) for PostgreSQL and H2released. HSQLDB is planned to be supported.
Hi all, Paragon Corporation escribió: Muhammad, We don't know too much about JASPA project, but here is our general feeling based on the underlying architecture and quick glance at the docs. From our general understanding JASPA implements all its PostgreSQL functions using Java stored procs. There are several consequences of this some good and some bad The Good 1) JASPA uses JTS directly so doesn't suffer from the need to port JTS functionality to GEOS. So you will probably see new features introduced in JTS in Jaspa sooner than you will see it in PostGIS. yes I think so but jaspa uses a slightly modifed version of JTS. Some changes: wkb/ewkb/wkt/ewkt M/Z handle Anyways, a JTS original library can be modified in less than one hour. 2) Good/Bad -- Not tied to a specific database platform (though it only really supports PostgreSQL/HSQL at the moment) -- I kind of consider this a good/bad of rasdaman as well. Its good in the sense that you can run it in other databases, but bad in the sense that your architecture is either more complicated and/or you don't play as nicely with the native functionality a database offers. JASPA its not quite as much of an issue as it is for rasdaman since the functions are implemented as PostgreSQL stored functions and can use just like you use PostGIS functions. I understand your point of view but one of the goal (I think the most important one) of jaspa is to aim to be easier to extend than postgis even for a not computer science expertice and to be able to use different databases. Im not talkig about plpgsql (this one is easy) but lwgeom, geos, native postgres funtions, etc. The proof that jaspa is easier to extend is: just with 22000 lines of code jaspa does almost everything postgis1.4 does. and its been developped just for one person in one year (of course thanks to the use of other open source projects as postgis, jts, geotools, etc.) I think it means jaspa is really easy to extend and to support new functionalities. Another point is GeoTools which I think its another stargate for new functionalities. 3) They seem to implement a lot of things above the core JTS - http://jaspa.forge.osor.eu/Introduction.html#id474917 Like Delaunay triangles and some topology. Sounds interesting. http://jaspa.forge.osor.eu/ST_DelaunayTriangles.html Jaspa supports some JTS 1.11 new functionality as you said for example delaunay triangles. Of course this is not a jaspa praiseworthy as it is a JTS merit. From jaspa 0.1 we will start developing new functionalities as: - hibrid topology - topology rules - cluster/tolerance - geodesic support They claim PostGIS 1.4 compatibility, but not sure if that means 100% -- it does look like they do support at least ST_Dump functions (and have implemented some of the PostGIS non-geography functions) http://jaspa.forge.osor.eu/differences.html#id498020 About PostGIS 1.4 compatibily I meant that almost all the postgis methods are implemented in jaspa. It is not 100% but it is close to that. Around 94% of the postgis methods work in jaspa. Im working in postgis long transaction methods (they are already implemented but i need a pljava patch to support to cancel triggers..luca ferrari did one already)..with the long transaction methods the supported functions are 97%. The Bad While PostgreSQL does support java stored procs, its not a common choice. I'm not sure exactly why. As I recall a while back -- it had something to do with the way Java classes were created and so forth similar to why .NET is not a supported option in PostgreSQL. Times have changed though so may not be a bad option now. This means A) Most likely it will be harder to install on all platforms. yes I agree, but Pljava will support postgres 9 at least. Hope jaspa project helps to keep pljava project running too. Anyways, we are just talking about postgres but H2 is easier in this sense and I hope we can develop jaspa for hsqldb soon. The huge problem now: h2 and hsqldb do not support spatial indexes. B) Not as tried and tested as C binding functions. So things such as memory management between the core PostgreSQL layer and library layer I suspect will be better in PostGIS than they are in JASPA. yes I think so. pljava is not as fast as directly accessing sql types from c and jaspa is not c. Anyways if you check the performance its not bad at all, and the performance about some methods can be surprising. C) There are lots of functions implemented directly in PostGIS that are not in JTS or GEOS (and GEOS is not a direct port in and of itself of JTS -- it has some 3D glue that JTS lacks). Example are my favorite functions implemented directly in PostGIS - All the geography support introduced in PostGIS 1.5, ST_ClosestPoint (native PostGIS), ST_AsGML/ST_GeomFromGML/KML, ST_Dump functions etc. I would say about 50 percent of the functions in PostGIS are not derived from JTS/GEOS though there are some overlaps
[postgis-users] PostGIS to SpatiaLite
Hi all, I need to copy several PostGIS tables to a SpatiaLite file, so it can be used on field laptops. A similar question appeared a year ago[1], but there were no solutions. I understand that ogr2ogr has support for SpatiaLite using GDAL 1.7.x, however I cannot seem to configure the GDAL source to enable SpatiaLite[2]. Yet another option is FME, but I don't have time/budget for this. FDO doe Are there any other freely available server-side tools to do the conversion? Thanks. -Mike [1] http://postgis.refractions.net/pipermail/postgis-users/2009-June/023707.html [2] http://lists.osgeo.org/pipermail/gdal-dev/2010-July/025448.html ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] PostGIS to SpatiaLite
Ok ok, I've finally tamed ogr2ogr to a working state. When configuring GDAL, use --with-spatialite=/usr/local (for a locally installed SpatiaLite library), then make and install GDAL (note my build attempt with Ubuntu 10.04 was unsuccessful for reasons I don't have time to care for, but my build with 9.04 works). To create the SpatiaLite file export the first layer: $ ogr2ogr -f SQLite -dsco SPATIALITE=yes myfile.sqlite PG:dbname=mydb user=myuser password=mypass myschema.mytable And for additional layer/tables: $ ogr2ogr -f SQLite -update myfile.db PG:dbname=mydb user=myuser password=mypass myschema.my2ndtable The resulting file work with viewers, including SpatiaLite GUI/GIS, Quantum GIS. I've started http://trac.osgeo.org/postgis/wiki/SpatiaLite -Mike On 24 July 2010 10:43, Mike Toews mwto...@gmail.com wrote: Hi all, I need to copy several PostGIS tables to a SpatiaLite file, so it can be used on field laptops. A similar question appeared a year ago[1], but there were no solutions. I understand that ogr2ogr has support for SpatiaLite using GDAL 1.7.x, however I cannot seem to configure the GDAL source to enable SpatiaLite[2]. Yet another option is FME, but I don't have time/budget for this. FDO doe Are there any other freely available server-side tools to do the conversion? Thanks. -Mike [1] http://postgis.refractions.net/pipermail/postgis-users/2009-June/023707.html [2] http://lists.osgeo.org/pipermail/gdal-dev/2010-July/025448.html ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] speeding up not in query
I have one spatial table and one non-spatial table each with about 380k rows. They can be joined by parcelnum. However, there are some parcel numbers in each table that do not match. I want the full rows of the non-matching from each. I've made a standard (b-tree) index for parcelnum in both tables and then ran this query to get non-matching rows in a single direction: SELECT * FROM survey WHERE survey.parcelnum NOT IN (SELECT parcel_esri.parcelnum FROM parcels_esri) This query has already run for 35 minutes and is still running. I have a laptop running Ubuntu 9.04 64bit with two P8700 2.53GHz CPUs and 3.8GB of memory. Only 1 CPU and 1.3GB of memory is being used. Is there anything I can do to speed up not-in (and join) queries? thank you, -david ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] JASPA (JAva SPATial) for PostgreSQL and H2released. HSQLDB is planned to be supported.
One solid point that I was keen to look so far is that the new opportunity for integration java-based technologies with the database, for instance, as Jose mentioned the geotools. Furthermore, since Oracle is the leading commercial database solution provider and it supports the java technology so a new opportunity for attracting the professionals that may lead to the emerging of bigger communities. Best, Imran --- On Sat, 7/24/10, José Carlos Martínez jomar...@cgf.upv.es wrote: From: José Carlos Martínez jomar...@cgf.upv.es Subject: Re: [postgis-users] JASPA (JAva SPATial) for PostgreSQL and H2released. HSQLDB is planned to be supported. To: PostGIS Users Discussion postgis-users@postgis.refractions.net Date: Saturday, July 24, 2010, 11:11 PM Hi all, Paragon Corporation escribió: Muhammad, We don't know too much about JASPA project, but here is our general feeling based on the underlying architecture and quick glance at the docs. From our general understanding JASPA implements all its PostgreSQL functions using Java stored procs. There are several consequences of this some good and some bad The Good 1) JASPA uses JTS directly so doesn't suffer from the need to port JTS functionality to GEOS. So you will probably see new features introduced in JTS in Jaspa sooner than you will see it in PostGIS. yes I think so but jaspa uses a slightly modifed version of JTS. Some changes: wkb/ewkb/wkt/ewkt M/Z handle Anyways, a JTS original library can be modified in less than one hour. 2) Good/Bad -- Not tied to a specific database platform (though it only really supports PostgreSQL/HSQL at the moment) -- I kind of consider this a good/bad of rasdaman as well. Its good in the sense that you can run it in other databases, but bad in the sense that your architecture is either more complicated and/or you don't play as nicely with the native functionality a database offers. JASPA its not quite as much of an issue as it is for rasdaman since the functions are implemented as PostgreSQL stored functions and can use just like you use PostGIS functions. I understand your point of view but one of the goal (I think the most important one) of jaspa is to aim to be easier to extend than postgis even for a not computer science expertice and to be able to use different databases. Im not talkig about plpgsql (this one is easy) but lwgeom, geos, native postgres funtions, etc. The proof that jaspa is easier to extend is: just with 22000 lines of code jaspa does almost everything postgis1.4 does. and its been developped just for one person in one year (of course thanks to the use of other open source projects as postgis, jts, geotools, etc.) I think it means jaspa is really easy to extend and to support new functionalities. Another point is GeoTools which I think its another stargate for new functionalities. 3) They seem to implement a lot of things above the core JTS - http://jaspa.forge.osor.eu/Introduction.html#id474917 Like Delaunay triangles and some topology. Sounds interesting. http://jaspa.forge.osor.eu/ST_DelaunayTriangles.html Jaspa supports some JTS 1.11 new functionality as you said for example delaunay triangles. Of course this is not a jaspa praiseworthy as it is a JTS merit. From jaspa 0.1 we will start developing new functionalities as: - hibrid topology - topology rules - cluster/tolerance - geodesic support They claim PostGIS 1.4 compatibility, but not sure if that means 100% -- it does look like they do support at least ST_Dump functions (and have implemented some of the PostGIS non-geography functions) http://jaspa.forge.osor.eu/differences.html#id498020 About PostGIS 1.4 compatibily I meant that almost all the postgis methods are implemented in jaspa. It is not 100% but it is close to that. Around 94% of the postgis methods work in jaspa. Im working in postgis long transaction methods (they are already implemented but i need a pljava patch to support to cancel triggers..luca ferrari did one already)..with the long transaction methods the supported functions are 97%. The Bad While PostgreSQL does support java stored procs, its not a common choice. I'm not sure exactly why. As I recall a while back -- it had something to do with the way Java classes were created and so forth similar to why .NET is not a supported option in PostgreSQL. Times have changed though so may not be a bad option now. This means A) Most likely it will be harder to install on all platforms. yes I agree, but Pljava will support postgres 9 at least. Hope jaspa project helps to keep pljava project running too. Anyways, we are just talking about postgres but H2 is easier in this sense and I hope we can develop jaspa for hsqldb soon. The huge problem now: h2 and hsqldb do not support spatial indexes. B) Not as tried and tested as C
Re: [postgis-users] PostGIS to SpatiaLite
I havent tried it much, but I know QGIS has this option. The OGR module does this. It is very good. Hope it works for you On Sat, Jul 24, 2010 at 4:26 PM, Mike Toews mwto...@gmail.com wrote: Ok ok, I've finally tamed ogr2ogr to a working state. When configuring GDAL, use --with-spatialite=/usr/local (for a locally installed SpatiaLite library), then make and install GDAL (note my build attempt with Ubuntu 10.04 was unsuccessful for reasons I don't have time to care for, but my build with 9.04 works). To create the SpatiaLite file export the first layer: $ ogr2ogr -f SQLite -dsco SPATIALITE=yes myfile.sqlite PG:dbname=mydb user=myuser password=mypass myschema.mytable And for additional layer/tables: $ ogr2ogr -f SQLite -update myfile.db PG:dbname=mydb user=myuser password=mypass myschema.my2ndtable The resulting file work with viewers, including SpatiaLite GUI/GIS, Quantum GIS. I've started http://trac.osgeo.org/postgis/wiki/SpatiaLite -Mike On 24 July 2010 10:43, Mike Toews mwto...@gmail.com wrote: Hi all, I need to copy several PostGIS tables to a SpatiaLite file, so it can be used on field laptops. A similar question appeared a year ago[1], but there were no solutions. I understand that ogr2ogr has support for SpatiaLite using GDAL 1.7.x, however I cannot seem to configure the GDAL source to enable SpatiaLite[2]. Yet another option is FME, but I don't have time/budget for this. FDO doe Are there any other freely available server-side tools to do the conversion? Thanks. -Mike [1] http://postgis.refractions.net/pipermail/postgis-users/2009-June/023707.html [2] http://lists.osgeo.org/pipermail/gdal-dev/2010-July/025448.html ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] speeding up not in query
You might want to try a not exists constraint, like this: SELECT * FROM survey WHERE NOT EXISTS (SELECT parcel_esri.parcelnum FROM parcels_esri WHERE parcel_esri.parcelnum = survey.parcelnum) On Sat, Jul 24, 2010 at 2:09 PM, David Epstein david...@umich.edu wrote: I have one spatial table and one non-spatial table each with about 380k rows. They can be joined by parcelnum. However, there are some parcel numbers in each table that do not match. I want the full rows of the non-matching from each. I've made a standard (b-tree) index for parcelnum in both tables and then ran this query to get non-matching rows in a single direction: SELECT * FROM survey WHERE survey.parcelnum NOT IN (SELECT parcel_esri.parcelnum FROM parcels_esri) This query has already run for 35 minutes and is still running. I have a laptop running Ubuntu 9.04 64bit with two P8700 2.53GHz CPUs and 3.8GB of memory. Only 1 CPU and 1.3GB of memory is being used. Is there anything I can do to speed up not-in (and join) queries? thank you, -david ___ 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] speeding up not in query
David, What does your query plan look like? Do you have an indeed in place for the parcelnum columns and are they the same data type. You may want to verify the index is being used by looking at the query plan. We have run into similar issues which we documented here. http://www.postgresonline.com/journal/archives/149-Forcing-the-planners-hand -with-set-enable_seqscan-off-WTF.html But couldn't figure out under what circumstances this happens. IN optimizations have changed a lot over various versions of PostgreSQL. Which version are you using? 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 Michael Smedberg Sent: Saturday, July 24, 2010 10:06 PM To: PostGIS Users Discussion Subject: Re: [postgis-users] speeding up not in query You might want to try a not exists constraint, like this: SELECT * FROM survey WHERE NOT EXISTS (SELECT parcel_esri.parcelnum FROM parcels_esri WHERE parcel_esri.parcelnum = survey.parcelnum) On Sat, Jul 24, 2010 at 2:09 PM, David Epstein david...@umich.edu wrote: I have one spatial table and one non-spatial table each with about 380k rows. They can be joined by parcelnum. However, there are some parcel numbers in each table that do not match. I want the full rows of the non-matching from each. I've made a standard (b-tree) index for parcelnum in both tables and then ran this query to get non-matching rows in a single direction: SELECT * FROM survey WHERE survey.parcelnum NOT IN (SELECT parcel_esri.parcelnum FROM parcels_esri) This query has already run for 35 minutes and is still running. I have a laptop running Ubuntu 9.04 64bit with two P8700 2.53GHz CPUs and 3.8GB of memory. Only 1 CPU and 1.3GB of memory is being used. Is there anything I can do to speed up not-in (and join) queries? thank you, -david ___ 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] speeding up not in query
Even better idea since you are looking for non matches. Use a LEFT JOIN SELECT survey.* FROM survey LEFT JOIN parcel_esri.parcelnum ON (survey.parcelnum = parcel_esri.parcelnum) WHERE parcel_esri.primary_key IS NULL; Your primary key stuff a field that never has null values. Leo and Regina http://www.postgis.us -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of David Epstein Sent: Saturday, July 24, 2010 5:09 PM To: postgis-users@postgis.refractions.net Subject: [postgis-users] speeding up not in query I have one spatial table and one non-spatial table each with about 380k rows. They can be joined by parcelnum. However, there are some parcel numbers in each table that do not match. I want the full rows of the non-matching from each. I've made a standard (b-tree) index for parcelnum in both tables and then ran this query to get non-matching rows in a single direction: SELECT * FROM survey WHERE survey.parcelnum NOT IN (SELECT parcel_esri.parcelnum FROM parcels_esri) This query has already run for 35 minutes and is still running. I have a laptop running Ubuntu 9.04 64bit with two P8700 2.53GHz CPUs and 3.8GB of memory. Only 1 CPU and 1.3GB of memory is being used. Is there anything I can do to speed up not-in (and join) queries? thank you, -david ___ 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
