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
