TransWikia.com

SELECT * will not work with table containing geodata

Geographic Information Systems Asked by fred randall on December 29, 2020

The following will not run… I get ORA-00932 error every time..

$sql = 'SELECT * FROM app.table_1';

however, this will work (specifying a column name, i.e. objectid):

$sql = 'SELECT objectid FROM app.table_1';

Seeing as I have a table with ~100 columns I do not want to manually hardcode out a list of the columns for this query.. anybody experience this?

legacy question: https://stackoverflow.com/questions/63562690/cant-select-ora-00932-inconsistent-datatypes-expected-char-got-adt

One Answer

When you say your table contains geodata, I am going to assume you mean it has one or several columns of type SDO_GEOMETRY (Oracle's native geospatial type).

The spatial type (SDO_GEOMETRY) is an object type that internally contains all the information needed to describe a geospatial shape: its type, its coordinate system, its structure and all the coordinates that form the shape.

So, the SELECT statement returns an object type, and your application code must be ready to handle it. It appears that your language environment requests the geospatial object to be returned as a string. But no automatic mapping of the geospatial object to string, ands you get the ORA-00932 error. Your application expects a string (CHAR) but got an object (ADT or Abstract Data Type).

A possible solution is simply not to select the object columns, if your application does not do anything with them.

The other solution is to explicitly serialize the objects to one of the industry-standard string notations for geospatial data. For that you can use functions from the SDO_UTIL package:

  • WKT (Well-Known Text): This is a very old text format, but still very common in GIS. Use SDO_UTIL.TO_WKTGEOMETRY() to produce it.
  • GML (Geographic Markup Language): an XML specialization for geospatial data. Use SDO_UTIL.TO_GMLGEOMETRY() to produce it.
  • GeoJSON: a JSON specialization for geospatial data. More modern and adopted by the newer GIS APIs and tools. Use SDO_UTIL.TO_GEOJSON() to produce it.

All three functions generate string output. But watch out: the output is a CLOB so your language environment must be able to use those. And a geospatial object can be large: thousands of points, 10's of thousands, hundreds of thousands ... depending on the nature of the geospatial data you manage.

There is one more possibility, if your geospatial objects are points. Points only have one pair of x,y coordinates (or a triple x,y,z if you have 3D data). In this case, you can just extract X and Y (and Z) from the geospatial objects, like this:

select ... g.geom.sdo_point.x as x, g.geom.sdo_point.y as y, ...
from geospatial_table g

This assumes the geospatial column is called geom. But the important point here is to USE AN ALIAS (here g). It is required for the SQL parser to correctly extract the content of the object type.

One more possibility is then to define a view with the above syntax (the SDO_UTIL function of the X/Y extraction). This then allows you to use SELECT * in your application.

Correct answer by Albert Godfrind on December 29, 2020

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP