OpenJump (JTS): Unknown WKB type 48

[aperi2007]

Hi, try'ing to open with qgis some layer in an instance of postgis200 r7518 (for windows).

QGis don't show nothing in the canvas without report any error.

But If i try with OpenJump? (more verbose of qgis) it report this error:

---

com.vividsolutions.jts.io.ParseException?: Unknown WKB type 48

at com.vividsolutions.jts.io.WKBReader.readGeometry(WKBReader.java:186) at com.vividsolutions.jts.io.WKBReader.read(WKBReader.java:140) at com.vividsolutions.jts.io.WKBReader.read(WKBReader.java:121) at com.vividsolutions.jump.datastore.postgis.PostgisValueConverterFactory?$WKBGeometryValueConverter.getValue(PostgisValueConverterFactory?.java:73) at com.vividsolutions.jump.datastore.postgis.PostgisResultSetConverter?.getFeature(PostgisResultSetConverter?.java:47) at com.vividsolutions.jump.datastore.postgis.PostgisFeatureInputStream?.getFeature(PostgisFeatureInputStream?.java:78) at com.vividsolutions.jump.datastore.postgis.PostgisFeatureInputStream?.readNext(PostgisFeatureInputStream?.java:72) at com.vividsolutions.jump.io.BaseFeatureInputStream?.hasNext(BaseFeatureInputStream?.java:38) at com.vividsolutions.jump.workbench.ui.plugin.datastore.RunDatastoreQueryPlugIn?.createLayer(RunDatastoreQueryPlugIn?.java:66) at com.vividsolutions.jump.workbench.ui.plugin.datastore.RunDatastoreQueryPlugIn?.createLayerable(RunDatastoreQueryPlugIn?.java:30) at com.vividsolutions.jump.workbench.ui.plugin.datastore.AbstractAddDatastoreLayerPlugIn?.run(AbstractAddDatastoreLayerPlugIn?.java:32) at com.vividsolutions.jump.workbench.ui.task.TaskMonitorManager?$TaskWrapper?.run(TaskMonitorManager?.java:151) at java.lang.Thread.run(Unknown Source)

---

It seem there is some problem in the WKB format. Is it changed ?

[strk]

For QGIS, see #1100. Let's keep this one for OpenJump?

[strk]

Is this with data loaded with the _new_ postgis or pre-existing at the time of postgis install ? The serialized format changed, so if you had data in your db it may be misinterpreted.

[aperi2007]

its a new DB create just with the same r7518.

To more test. I load a shapefile using shp2pgsql and try it with openjump. It report the same error.

com.vividsolutions.jts.io.ParseException?: Unknown WKB type 48

[strk]

What are the ZM flags of your geometries ?

[aperi2007]

Replying to strk:

What are the ZM flags of your geometries ?

I have tested three distinct datasets. Always they return

com.vividsolutions.jts.io.ParseException??: Unknown WKB type 48

The first is a line-2D dataset that has ZM flags = 0

the second is a line-3D dataset that has ZM flags = 3

and the third is a poly-2D that has ZM flags = 0.

[pramsey]

Can you turn on statement logging in your data base and capture the SQL that OpenJUMP is generating to create the output geometries?

[aperi2007]

I'm not sure this the log you need. I apply the info level.

the query is:

select * from caricamento.line_2d;

after the query I stop the db instance to flush log file.

2011-07-05 23:14:50 CEST LOG:  00000: database system was shut down at 2011-07-05 23:12:15 CEST
2011-07-05 23:14:50 CEST LOCATION:  StartupXLOG, .\src\backend\access\transam\xlog.c:5720
2011-07-05 23:14:51 CEST LOG:  00000: database system is ready to accept connections
2011-07-05 23:14:51 CEST LOCATION:  reaper, .\src\backend\postmaster\postmaster.c:2407
2011-07-05 23:14:51 CEST LOG:  00000: autovacuum launcher started
2011-07-05 23:14:51 CEST LOCATION:  AutoVacLauncherMain, .\src\backend\postmaster\autovacuum.c:404
2011-07-05 23:15:24 CEST LOG:  00000: execute <unnamed>: select * from caricamento.line_2d
2011-07-05 23:15:24 CEST LOCATION:  exec_execute_message, .\src\backend\tcop\postgres.c:1978
2011-07-05 23:15:24 CEST LOG:  00000: execute <unnamed>: SELECT typname FROM pg_catalog.pg_type WHERE oid = $1
2011-07-05 23:15:24 CEST DETAIL:  parameters: $1 = '193912'
2011-07-05 23:15:24 CEST LOCATION:  exec_execute_message, .\src\backend\tcop\postgres.c:1978
2011-07-05 23:15:32 CEST LOG:  00000: received fast shutdown request
2011-07-05 23:15:32 CEST LOCATION:  pmdie, .\src\backend\postmaster\postmaster.c:2222
2011-07-05 23:15:32 CEST LOG:  00000: aborting any active transactions
2011-07-05 23:15:32 CEST LOCATION:  pmdie, .\src\backend\postmaster\postmaster.c:2240
2011-07-05 23:15:32 CEST LOG:  00000: autovacuum launcher shutting down
2011-07-05 23:15:32 CEST LOCATION:  AutoVacLauncherMain, .\src\backend\postmaster\autovacuum.c:760
2011-07-05 23:15:32 CEST LOG:  00000: shutting down
2011-07-05 23:15:32 CEST LOCATION:  ShutdownXLOG, .\src\backend\access\transam\xlog.c:6980
2011-07-05 23:15:32 CEST LOG:  00000: database system is shut down
2011-07-05 23:15:32 CEST LOCATION:  ShutdownXLOG, .\src\backend\access\transam\xlog.c:7002

[robe]

Okay I ran it too in quantum and am having same problem. Funny I guess when I did it with a point, I didn't have problems.

This is after loading up tl_2010_25_zcta510 with a fresh install of PostGIS today (forget the r). I turned on logging, and this is the query that Quantum is trying to return which returns nothing.

 select st_estimated_extent('public','tl_2010_25_zcta510','geom');
select "gid",st_asbinary("geom",'NDR') from "public"."tl_2010_25_zcta510" where "geom" && st_setsrid('BOX3D(-1.6754578754578755 -1.0000000000000000, 1.6754578754578755 1.0000000000000000)'::box3d,4269)

The key parts. So it seems something wrong with our estimated_extent maybe?

[robe]

If I compare the estimated extent to actual extent its way off.

select ST_Extent(geom) from tl_2010_25_zcta510;

yields:
BOX(-73.508142 41.230015,-69.923562 42.88679)

 select st_estimated_extent('public','tl_2010_25_zcta510','geom');

Yields:
BOX(59157111109.9236 0,0 0)

which also doesn't seem to agree with QGIS

The other commands it runs is this:

Just in case I missed an important query -- this is the full log from when I started using QGIS:

2011-07-05 21:34:59 EDT LOG:  statement: set client_encoding to 'UNICODE'
2011-07-05 21:34:59 EDT LOG:  statement: SET application_name='Quantum GIS'
2011-07-05 21:34:59 EDT LOG:  statement: select f_table_name,f_table_schema,f_geometry_column,upper(type),pg_class.relkind from geometry_columns,pg_class,pg_namespace where relname=f_table_name and f_table_schema=nspname and pg_namespace.oid=pg_class.relnamespace and has_schema_privilege(pg_namespace.nspname,'usage') and has_table_privilege('"'||pg_namespace.nspname||'"."'||pg_class.relname||'"','select') order by f_table_schema,f_table_name,f_geometry_column
2011-07-05 21:34:59 EDT LOG:  statement: select f_table_name,f_table_schema,f_geography_column,upper(type),pg_class.relkind from geography_columns,pg_class,pg_namespace where relname=f_table_name and f_table_schema=nspname and pg_namespace.oid=pg_class.relnamespace and has_schema_privilege(pg_namespace.nspname,'usage') and has_table_privilege('"'||pg_namespace.nspname||'"."'||pg_class.relname||'"','select') order by f_table_schema,f_table_name,f_geography_column
2011-07-05 21:34:59 EDT LOG:  statement: select pg_class.relname,pg_namespace.nspname,pg_attribute.attname,pg_class.relkind from pg_attribute,pg_class,pg_namespace where pg_namespace.oid=pg_class.relnamespace and pg_attribute.attrelid = pg_class.oid and ( exists (select * from pg_type WHERE pg_type.oid=pg_attribute.atttypid AND pg_type.typname IN ('geometry','geography')) or pg_attribute.atttypid IN (select oid FROM pg_type a WHERE EXISTS (SELECT * FROM pg_type b WHERE a.typbasetype=b.oid AND b.typname IN ('geometry','geography')))) and has_schema_privilege( pg_namespace.nspname, 'usage' ) and has_table_privilege( '"' || pg_namespace.nspname || '"."' || pg_class.relname || '"', 'select' ) and not exists (select * from geometry_columns WHERE pg_namespace.nspname=f_table_schema AND pg_class.relname=f_table_name) and not exists (select * from geography_columns WHERE pg_namespace.nspname=f_table_schema AND pg_class.relname=f_table_name) and pg_class.relkind in( 'v', 'r' )
2011-07-05 21:34:59 EDT LOG:  statement: set client_encoding to 'UNICODE'
2011-07-05 21:34:59 EDT LOG:  statement: SET application_name='Quantum GIS'
2011-07-05 21:34:59 EDT LOG:  statement: select distinct case when geometrytype("extent") IN ('POINT','MULTIPOINT') THEN 'POINT' when geometrytype("extent") IN ('LINESTRING','MULTILINESTRING') THEN 'LINESTRING' when geometrytype("extent") IN ('POLYGON','MULTIPOLYGON') THEN 'POLYGON' end from "public"."raster_columns"
2011-07-05 21:35:02 EDT LOG:  statement: set client_encoding to 'UNICODE'
2011-07-05 21:35:02 EDT LOG:  statement: select postgis_version()
2011-07-05 21:35:02 EDT LOG:  statement: select postgis_geos_version(),postgis_proj_version()
2011-07-05 21:35:02 EDT LOG:  statement: SET application_name='Quantum GIS'
2011-07-05 21:35:02 EDT LOG:  statement: select postgis_version()
2011-07-05 21:35:02 EDT LOG:  statement: select postgis_geos_version(),postgis_proj_version()
2011-07-05 21:35:02 EDT LOG:  statement: select * from "public"."tl_2010_25_zcta510" limit 1
2011-07-05 21:35:02 EDT LOG:  statement: SELECT pg_is_in_recovery()
2011-07-05 21:35:02 EDT LOG:  statement: SELECT has_table_privilege('"public"."tl_2010_25_zcta510"','DELETE'),has_any_column_privilege('"public"."tl_2010_25_zcta510"','UPDATE'),has_column_privilege('"public"."tl_2010_25_zcta510"','geom','UPDATE'),has_table_privilege('"public"."tl_2010_25_zcta510"','INSERT'),current_schema()
2011-07-05 21:35:02 EDT LOG:  statement: SELECT 1 FROM pg_class,pg_namespace WHERE pg_class.relnamespace=pg_namespace.oid AND pg_get_userbyid(relowner)=current_user AND relname='tl_2010_25_zcta510' AND nspname='public'
2011-07-05 21:35:02 EDT LOG:  statement: select type,srid from geometry_columns where f_table_name='tl_2010_25_zcta510' and f_geometry_column='geom' and f_table_schema='public'
2011-07-05 21:35:02 EDT LOG:  statement: select regclass('"public"."tl_2010_25_zcta510"')::oid
2011-07-05 21:35:02 EDT LOG:  statement: BEGIN READ ONLY
2011-07-05 21:35:02 EDT LOG:  statement: declare oidcursor binary cursor for select regclass('"public"."tl_2010_25_zcta510"')::oid
2011-07-05 21:35:02 EDT LOG:  statement: fetch forward 1 from oidcursor
2011-07-05 21:35:02 EDT LOG:  statement: CLOSE oidcursor
2011-07-05 21:35:02 EDT LOG:  statement: COMMIT
2011-07-05 21:35:02 EDT LOG:  statement: select indkey from pg_index where indisunique and indrelid=regclass('"public"."tl_2010_25_zcta510"')::oid and indpred is null
2011-07-05 21:35:02 EDT LOG:  statement: select attname,pg_type.typname from pg_attribute,pg_type where atttypid=pg_type.oid and attnum=1 and attrelid=regclass('"public"."tl_2010_25_zcta510"')
2011-07-05 21:35:02 EDT LOG:  statement: SELECT column_default FROM information_schema.columns WHERE column_default IS NOT NULL AND table_schema = 'public' AND table_name = 'tl_2010_25_zcta510' AND column_name = 'gid'
2011-07-05 21:35:02 EDT LOG:  statement: SELECT regclass('"public"."tl_2010_25_zcta510"')::oid
2011-07-05 21:35:02 EDT LOG:  statement: SELECT description FROM pg_description WHERE objoid=135250 AND objsubid=0
2011-07-05 21:35:02 EDT LOG:  statement: select * from "public"."tl_2010_25_zcta510" limit 0
2011-07-05 21:35:02 EDT LOG:  statement: SELECT typname,typtype,typelem,typlen FROM pg_type WHERE oid=23
2011-07-05 21:35:02 EDT LOG:  statement: SELECT attnum FROM pg_attribute WHERE attrelid=135250 AND attname='gid'
2011-07-05 21:35:02 EDT LOG:  statement: SELECT description FROM pg_description WHERE objoid=135250 AND objsubid=1
2011-07-05 21:35:02 EDT LOG:  statement: SELECT typname,typtype,typelem,typlen FROM pg_type WHERE oid=1043
2011-07-05 21:35:02 EDT LOG:  statement: SELECT attnum FROM pg_attribute WHERE attrelid=135250 AND attname='statefp10'
2011-07-05 21:35:02 EDT LOG:  statement: SELECT description FROM pg_description WHERE objoid=135250 AND objsubid=2
2011-07-05 21:35:02 EDT LOG:  statement: SELECT typname,typtype,typelem,typlen FROM pg_type WHERE oid=1043
2011-07-05 21:35:02 EDT LOG:  statement: SELECT attnum FROM pg_attribute WHERE attrelid=135250 AND attname='zcta5ce10'
2011-07-05 21:35:02 EDT LOG:  statement: SELECT description FROM pg_description WHERE objoid=135250 AND objsubid=3
2011-07-05 21:35:02 EDT LOG:  statement: SELECT typname,typtype,typelem,typlen FROM pg_type WHERE oid=1043
2011-07-05 21:35:02 EDT LOG:  statement: SELECT attnum FROM pg_attribute WHERE attrelid=135250 AND attname='geoid10'
2011-07-05 21:35:02 EDT LOG:  statement: SELECT description FROM pg_description WHERE objoid=135250 AND objsubid=4
2011-07-05 21:35:02 EDT LOG:  statement: SELECT typname,typtype,typelem,typlen FROM pg_type WHERE oid=1043
2011-07-05 21:35:02 EDT LOG:  statement: SELECT attnum FROM pg_attribute WHERE attrelid=135250 AND attname='classfp10'
2011-07-05 21:35:02 EDT LOG:  statement: SELECT description FROM pg_description WHERE objoid=135250 AND objsubid=5
2011-07-05 21:35:02 EDT LOG:  statement: SELECT typname,typtype,typelem,typlen FROM pg_type WHERE oid=1043
2011-07-05 21:35:02 EDT LOG:  statement: SELECT attnum FROM pg_attribute WHERE attrelid=135250 AND attname='mtfcc10'
2011-07-05 21:35:02 EDT LOG:  statement: SELECT description FROM pg_description WHERE objoid=135250 AND objsubid=6
2011-07-05 21:35:02 EDT LOG:  statement: SELECT typname,typtype,typelem,typlen FROM pg_type WHERE oid=1043
2011-07-05 21:35:02 EDT LOG:  statement: SELECT attnum FROM pg_attribute WHERE attrelid=135250 AND attname='funcstat10'
2011-07-05 21:35:02 EDT LOG:  statement: SELECT description FROM pg_description WHERE objoid=135250 AND objsubid=7
2011-07-05 21:35:02 EDT LOG:  statement: SELECT typname,typtype,typelem,typlen FROM pg_type WHERE oid=701
2011-07-05 21:35:02 EDT LOG:  statement: SELECT attnum FROM pg_attribute WHERE attrelid=135250 AND attname='aland10'
2011-07-05 21:35:02 EDT LOG:  statement: SELECT description FROM pg_description WHERE objoid=135250 AND objsubid=8
2011-07-05 21:35:02 EDT LOG:  statement: SELECT typname,typtype,typelem,typlen FROM pg_type WHERE oid=701
2011-07-05 21:35:02 EDT LOG:  statement: SELECT attnum FROM pg_attribute WHERE attrelid=135250 AND attname='awater10'
2011-07-05 21:35:02 EDT LOG:  statement: SELECT description FROM pg_description WHERE objoid=135250 AND objsubid=9
2011-07-05 21:35:02 EDT LOG:  statement: SELECT typname,typtype,typelem,typlen FROM pg_type WHERE oid=1043
2011-07-05 21:35:02 EDT LOG:  statement: SELECT attnum FROM pg_attribute WHERE attrelid=135250 AND attname='intptlat10'
2011-07-05 21:35:02 EDT LOG:  statement: SELECT description FROM pg_description WHERE objoid=135250 AND objsubid=10
2011-07-05 21:35:02 EDT LOG:  statement: SELECT typname,typtype,typelem,typlen FROM pg_type WHERE oid=1043
2011-07-05 21:35:02 EDT LOG:  statement: SELECT attnum FROM pg_attribute WHERE attrelid=135250 AND attname='intptlon10'
2011-07-05 21:35:02 EDT LOG:  statement: SELECT description FROM pg_description WHERE objoid=135250 AND objsubid=11
2011-07-05 21:35:02 EDT LOG:  statement: SELECT typname,typtype,typelem,typlen FROM pg_type WHERE oid=1043
2011-07-05 21:35:02 EDT LOG:  statement: SELECT attnum FROM pg_attribute WHERE attrelid=135250 AND attname='partflg10'
2011-07-05 21:35:02 EDT LOG:  statement: SELECT description FROM pg_description WHERE objoid=135250 AND objsubid=12
2011-07-05 21:35:02 EDT LOG:  statement: set client_min_messages to error
2011-07-05 21:35:02 EDT LOG:  statement: SELECT COUNT(*) FROM pg_stats WHERE schemaname='public' AND tablename='tl_2010_25_zcta510' AND attname='geom'
2011-07-05 21:35:02 EDT LOG:  statement: select reltuples::int from pg_catalog.pg_class where oid=regclass('"public"."tl_2010_25_zcta510"')::oid
2011-07-05 21:35:02 EDT LOG:  statement: select st_estimated_extent('public','tl_2010_25_zcta510','geom')
2011-07-05 21:35:02 EDT LOG:  statement: BEGIN READ ONLY
2011-07-05 21:35:02 EDT LOG:  statement: declare qgisf2 binary cursor for select "gid",st_asbinary("geom",'NDR') from "public"."tl_2010_25_zcta510" where "geom" && st_setsrid('BOX3D(-1.6754578754578755 -1.0000000000000000, 1.6754578754578755 1.0000000000000000)'::box3d,4269)
2011-07-05 21:35:02 EDT LOG:  statement: fetch forward 200 from qgisf2
2011-07-05 21:35:02 EDT LOG:  statement: CLOSE qgisf2
2011-07-05 21:35:02 EDT LOG:  statement: COMMIT

[robe]

As far as OpenJump? goes, I usually use the Ad-Hoc query tool and that works fine.

If I go the data layer way (hmm I think I have to upgrade my OpenJUMP since I thought some of these calls were fixed). I again get nothing with last night build of PostGIS and queries look like

2011-07-05 21:53:31 EDT LOG:  execute <unnamed>: SELECT DISTINCT f_table_schema, f_table_name FROM geometry_columns
2011-07-05 21:53:32 EDT LOG:  execute <unnamed>: SELECT f_geometry_column FROM geometry_columns WHERE lower(f_table_name) = 'tl_2010_25_zcta510'
2011-07-05 21:53:34 EDT LOG:  execute <unnamed>: SELECT f_geometry_column FROM geometry_columns WHERE lower(f_table_name) = 'tl_2010_25_zcta510'
2011-07-05 21:53:36 EDT ERROR:  function find_extent(unknown, unknown) does not exist at character 17
2011-07-05 21:53:36 EDT HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
2011-07-05 21:53:36 EDT STATEMENT:  SELECT AsBinary(find_extent( 'tl_2010_25_zcta510', 'geom' ))
2011-07-05 21:53:36 EDT ERROR:  function find_extent(unknown, unknown) does not exist at character 17
2011-07-05 21:53:36 EDT HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
2011-07-05 21:53:36 EDT STATEMENT:  SELECT AsBinary(find_extent( 'tl_2010_25_zcta510', 'geom' ))
2011-07-05 21:53:39 EDT ERROR:  function find_extent(unknown, unknown) does not exist at character 17
2011-07-05 21:53:39 EDT HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
2011-07-05 21:53:39 EDT STATEMENT:  SELECT AsBinary(find_extent( 'tl_2010_25_zcta510', 'geom' ))
2011-07-05 21:53:39 EDT ERROR:  function find_extent(unknown, unknown) does not exist at character 17
2011-07-05 21:53:39 EDT HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
2011-07-05 21:53:39 EDT STATEMENT:  SELECT AsBinary(find_extent( 'tl_2010_25_zcta510', 'geom' ))

[robe]

Andrea -- how are you querying OpenJump?? I don't get the error you have above about wkb type, but I'm running a slightly newer PostGIS. With ad-hoc tool it gives that error if you don't do an ST_AsBinary on the column but that has always been the case.

SO:

-- this works
SELECT ST_AsBinary(geom)
FROM tl_2010_25_zcta510

-- this will give a type 48 error in ad-hoc tool
SELECT geom
FROM tl_2010_25_zcta510;

FWIW -- if I install the missing legacy functions, my OpenJump? works fine with the add data store layer option also. So I don't think there is an issue with OpenJump?, just QGIS.

[pramsey]

That speaks for leaving a few of the legacy functions in place...

[aperi2007]

Hi Robe,

using select st_asbinary(geom) from <table>

work well. I use a tool of OJ that need to write the query sql. So is quite simple to add the ST_AsBinary()

So I think this ticket could be closed as invalid

I agree with your opinion  http://postgis.refractions.net/pipermail/postgis-devel/2011-July/014258.html

Regards,

Replying to robe:

Andrea -- how are you querying OpenJump?? I don't get the error you have above about wkb type, but I'm running a slightly newer PostGIS. With ad-hoc tool it gives that error if you don't do an ST_AsBinary on the column but that has always been the case. SO: {{{ -- this works SELECT ST_AsBinary(geom) FROM tl_2010_25_zcta510 }}} {{{ -- this will give a type 48 error in ad-hoc tool SELECT geom FROM tl_2010_25_zcta510; }}} FWIW -- if I install the missing legacy functions, my OpenJump? works fine with the add data store layer option also. So I don't think there is an issue with OpenJump?, just QGIS.

[michaudm]

Note that version 1.4.1 of OpenJUMP, released a few days ago, includes a patch from Larry Reeder which makes the use of function ST_Binary(geom) optional in OpenJUMP's SQL ad-hoc tool,

Michaël