Hi,
I believe it comes from the OGC GeoPackage draft standard
https://portal.opengeospatial.org/files/?artifact_id=51391
-Jukka-
________________________________________
Lähettäjä: Michaël Michaud [michael.mich...@free.fr]
Lähetetty: 22. maaliskuuta 2013 0:02
Vastaanottaja: OpenJump develop and use
Aihe: Re: [JPP-Devel] PostGIS PlugIn Error executing query: CREATE INDEX
Hi,
Hmmm, embarassing,
I will drop the lower function in the queries shown below to be consistent
with the case sensitive general scheme,
Wonder why Spatialite choosed to store only lower case names in geometry
columns
By the way, the code which emitted your log is specific to PostgreSQL and
as far as I know, PostgreSQL accept lower, upper or mixed names in
geometry_columns.
Michaël
> Hi,
>
> Mixed case works as far as I can see OK when OpenJUMP creates new tables and
> queries are build correctly with double quotes. Table name is also written
> with mixed case into geometry_columns. However, it may cause troubles when
> geometry_columns are read because I can see these statements which are sent
> by OpenJUMP
>
> SELECT f_geometry_column FROM geometry_columns WHERE lower(f_table_schema) =
> 'public' AND lower(f_table_name) = 'uusi3'
> 2013-03-21 22:52:44 EET LOG: execute <unnamed>: SELECT
> ST_AsBinary(ST_Estimated_Extent( 'uUsi3', 'geom' ))
> 2013-03-21 22:52:44 EET LOG: execute <unnamed>: SELECT column_name FROM
> information_schema.columns WHERE lower(table_schema) = 'public' AND
> lower(table_name) = 'uusi3'
> 2013-03-21 22:52:44 EET LOG: execute <unnamed>: SELECT ST_AsEWKB("geom") as
> geom,"gid","cell_id","gid","cell_id" FROM "uUsi3" t WHERE "geom" &&
> ST_SetSRID('BOX3D(-76000.0 6570000.0,524000.0 6576000.0)'::box3d,0) LIMIT 1
>
> What happens it that my table name is "uUsi3". OpenJUMP makes a search from
> geometry columns with lower case comparison lower(f_table_name) = 'uusi3'. It
> is finding my "uUsi3" but it would find also "UUSI3", "Uusi3", "uuSi3" and so
> on.
>
> There seems to be some kind of assumption that there should not be several
> spatial tables with the same names after converting them to all lowercase and
> the same applies to the names of the geometry columns. They may be troubles
> if one table has two geometry columns named as "geom" and "Geom".
>
> Spatialite 4.0 is even using triggers which write only lowercase values into
> f_table_names and f_geometry_columns in the geometry_columns table, see
> https://www.gaia-gis.it/fossil/libspatialite/wiki?name=switching-to-4.0.
>
> I do not really know what to think about all this.
>
> -Jukka-
>
> ________________________________
> Lähettäjä: Michaël Michaud [michael.mich...@free.fr]
> Lähetetty: 21. maaliskuuta 2013 22:10
> Vastaanottaja: jump-pilot-devel@lists.sourceforge.net
> Aihe: Re: [JPP-Devel] PostGIS PlugIn Error executing query: CREATE INDEX
>
> Hi,
>
> You should know that currently, the PostGIS plugin
> works in a case-sensitive way.
> I choosed to double quote every schema/table names
> before sending queries.
> I know it is not the default behaviour of SQL, but it is
> possible and it appeared more simple to understand
> and to implement this way.
> I tried to manage both cases with/without double
> quotes but it made things much more complicated
> and error prone.
>
> I think that this way, you can manage lower case, upper
> case and mixed case table names as soon as you are
> aware of how it works.
>
> Let me know
> - if there is something broken from the above perspective
> - if you know use cases where it can really be a problem
>
> Michaël
>
>
>
> Hi Jukka,
>
> I am working with OJ 20130318 rev. 3364
> and with PostgreSQL 9.2.3 and PostGIS 2.0.2
>
> SELECT * from pg_indexes
>
> shows under attribute tablename the value new
> and under indexname the value new GEOMETRY idx
> (with blanks and not New_GEOMETRY_idx).
>
> Does the PostGIS PlugIn creates an index?
>
> Uwe
>
>
>
> Am 21.03.2013 14:23, schrieb Rahkonen Jukka:
> Hi,
>
> I can't reproduce the error with a bit old OpenJUMP version r3131, PostgreSQL
> 9.0 and PostGIS 1.5 by working in a normal way.
> No problem with saving a layer as "new", "New", "uusi" and "Uusi".
>
> However, I managed to get the same error message than you by creating an
> index as
> CREATE INDEX "New_GEOMETRY_idx" ON "new" USING GIST ( "GEOMETRY" )
>
> So I created an index named "New_GEOMETRY_idx" on existing table "new". Now I
> can't create new table "New" with OpenJUMP because there is already an index
> with that name, even it is an index for another table. Did you do what I was
> suggesting by running
>
> "SELECT * from pg_indexes where indexname='New_GEOMETRY_idx"
>
> -Jukka Rahkonen-
>
> Uwe Dalluege wrote:
>
> Hi Jukka,
>
> it seems that the PostGIS PlugIn creates an index on the geometry column.
> The problem are the tablenames with capital letters and small letters.
> But for the user is the tablename new = New but unfortunately not for
> Postgres.
>
> Maybe it is a solution that the plugin writes tablenames in small letters?
>
> What do you think?
>
> Regards
>
> Uwe
>
> Am 21.03.2013 13:09, schrieb Rahkonen Jukka:
> Hi,
>
> The SQL in the error message looks good and the error seems to come
> from PostgreSQL and it says that an index with the same name exists already.
> FEHLER: Relation New_GEOMETRY_idx existiert bereits
>
> Do "SELECT * from pg_indexes where indexname='New_GEOMETRY_idx"
> and make sure that such index does not really exist. If that is the case and
> you can repeat the error, turn on statement logging on PostrgreSQL side and
> check the exact SQL which is sent by OpenJUMP. The SQL from the error
> message shoud work OK " CREATE INDEX "New_GEOMETRY_idx" ON "New"
> USING GIST ( "GEOMETRY" )".
>
> -Jukka Rahkonen-
>
> Uwe Dalluege wrote:
>
> Hi,
>
> if the PostGIS database contains a table namend new (with small
> letter) and you try to save the layer New (with capital N) you receive the
> error:
>
>
> java.sql.SQLException: Error executing query: CREATE INDEX
> "New_GEOMETRY_idx" ON "New" USING GIST ( "GEOMETRY" )
> at
> org.openjump.core.ui.plugin.datastore.postgis.SaveToPostGISDataSource
> .cr
> eateAndPopulateTable(SaveToPostGISDataSource.java:341)
> at
> org.openjump.core.ui.plugin.datastore.postgis.SaveToPostGISDataSource
> .ac
> cess$400(SaveToPostGISDataSource.java:50)
> at
>
> org.openjump.core.ui.plugin.datastore.postgis.SaveToPostGISDataSource$1.
> executeUpdate(SaveToPostGISDataSource.java:183)
> at
>
> com.vividsolutions.jump.workbench.datasource.AbstractSaveDatasetAsPlu
> gI
> n.run(AbstractSaveDatasetAsPlugIn.java:33)
> at
>
> com.vividsolutions.jump.workbench.ui.task.TaskMonitorManager$TaskWrap
> per.run(TaskMonitorManager.java:152)
> at java.lang.Thread.run(Thread.java:722)
> Caused by: org.postgresql.util.PSQLException: FEHLER: Relation
> New_GEOMETRY_idx existiert bereits
> at
>
> org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryEx
> ec
> utorImpl.java:2157)
> at
>
> org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutor
> Im
> pl.java:1886)
> at
>
> org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.ja
> va
> :255)
> at
> org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Stat
> em
> ent.java:555)
> at
> org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(Abstract
> Jdb
> c2Statement.java:403)
> at
> org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Stat
> em
> ent.java:395)
> at
> org.openjump.core.ui.plugin.datastore.postgis.SaveToPostGISDataSource
> .cr
> eateAndPopulateTable(SaveToPostGISDataSource.java:339)
> ... 5 more
>
> Regards
>
> Uwe
>
>
> ---------------------------------------------------------------------
> --------- Everyone hates slow websites. So do we.
> Make your web apps faster with AppDynamics Download AppDynamics
> Lite
> for free today:
> http://p.sf.net/sfu/appdyn_d2d_mar
> _______________________________________________
> Jump-pilot-devel mailing list
> Jump-pilot-devel@lists.sourceforge.net<mailto:Jump-pilot-devel@lists.sourceforge.net>
> https://lists.sourceforge.net/lists/listinfo/jump-pilot-devel
>
> ----------------------------------------------------------------------
> -------- Everyone hates slow websites. So do we.
> Make your web apps faster with AppDynamics Download AppDynamics Lite
> for free today:
> http://p.sf.net/sfu/appdyn_d2d_mar
> _______________________________________________
> Jump-pilot-devel mailing list
> Jump-pilot-devel@lists.sourceforge.net<mailto:Jump-pilot-devel@lists.sourceforge.net>
> https://lists.sourceforge.net/lists/listinfo/jump-pilot-devel
>
>
> ------------------------------------------------------------------------------
> Everyone hates slow websites. So do we.
> Make your web apps faster with AppDynamics
> Download AppDynamics Lite for free today:
> http://p.sf.net/sfu/appdyn_d2d_mar
> _______________________________________________
> Jump-pilot-devel mailing list
> Jump-pilot-devel@lists.sourceforge.net<mailto:Jump-pilot-devel@lists.sourceforge.net>
> https://lists.sourceforge.net/lists/listinfo/jump-pilot-devel
>
>
>
>
>
> ------------------------------------------------------------------------------
> Everyone hates slow websites. So do we.
> Make your web apps faster with AppDynamics
> Download AppDynamics Lite for free today:
> http://p.sf.net/sfu/appdyn_d2d_mar
>
>
>
> _______________________________________________
> Jump-pilot-devel mailing list
> Jump-pilot-devel@lists.sourceforge.net<mailto:Jump-pilot-devel@lists.sourceforge.net>
> https://lists.sourceforge.net/lists/listinfo/jump-pilot-devel
>
>
>
> ------------------------------------------------------------------------------
> Everyone hates slow websites. So do we.
> Make your web apps faster with AppDynamics
> Download AppDynamics Lite for free today:
> http://p.sf.net/sfu/appdyn_d2d_mar
> _______________________________________________
> Jump-pilot-devel mailing list
> Jump-pilot-devel@lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/jump-pilot-devel
>
>
------------------------------------------------------------------------------
Everyone hates slow websites. So do we.
Make your web apps faster with AppDynamics
Download AppDynamics Lite for free today:
http://p.sf.net/sfu/appdyn_d2d_mar
_______________________________________________
Jump-pilot-devel mailing list
Jump-pilot-devel@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/jump-pilot-devel
