Use wkb with postgis

Description

Postgis should at least have the option to use well known binary directly, as it's much faster. Work was started on a branch in postgis, but never completed.

There are two ways to use wkb, the fastest is to declare a binary cursor, but the problem is that all data is then returned as binary. One way to get around this is to append ::text to each field requested, so the query would be "select "name"::text, "gid"::text, asBinary("the_geom"). This needs to be tested with all types though. The problem is it turns everything to strings instead of more appropriate types, so we might have to do some parsing.

The second way is to use a text cursor, which means the geometry binary is returned as hex. This is still faster than reading well known text. The hex string must then be converted to binary. The way to do this is take the String (or char[]) WKB, make a byte[] that's half the length of the WKB string, for each 2-char pair of WKB convert to a single byte.

The conversion function will be something like a*16+b There's an example of this WKB_in() in postgis_inout.c

thanks to David Blasby for showing how to do this, I just need to implement it.

One more idea that might make sense with our new reader stuff is to have two resultSets, one with a binary cursor, one without. The problem with this is order and timing, if an insert went in between the two statements or something, that could mess up results. And we might need an ORDER BY statement to make sure they were in the right order, but that could slow things down a bit.

Best way to handle all these is probably to implement a few strategies and allow users to specify in the DataSource params which one they want.

Environment

None

Activity

Show:
codehaus
April 10, 2015, 3:28 PM

CodeHaus Comment From: cholmes - Time: Mon, 1 Dec 2003 16:27:42 -0600
---------------------
From refractions:

From <a href='kneufeld@refractions.net'>kneufeld@refractions.net</a> Mon Dec 1 17:25:45 2003

Date: Mon, 01 Dec 2003 13:27:41 -0800

From: Kevin Neufeld &lt;<a href='kneufeld@refractions.net'>kneufeld@refractions.net</a>&gt;

To: David Blasby &lt;<a href='dblasby@refractions.net'>dblasby@refractions.net</a>&gt;

Subject: Re: as per last email

Here is the code that will convert a String of hex characters that

represents the WTB form of a geometry into a JTS Geometry object.

You need to have my modified version of wkb4j.jar in order to run this

code.

&nbsp;&nbsp;&nbsp;&nbsp;/**

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;* This method will convert a String of hex characters that

represent the hexadecimal

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;* form of a Geometry in Well Known Binary representation to a JTS

Geometry object.

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;* @param wkbBytes a String of hex characters where each character

represents a hex value.

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;* In particular, each character is a value of 0-9, A, B ,C, D, E,

or F.

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;* @return a JTS Geometry object that is equivalent to the WTB

representation passed in

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;* by param wkb

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;* @throws Exception if more than one geometry object was found in

the WTB representation,

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;* or if the parser could not parse the WKB representation.

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;*/

&nbsp;&nbsp;&nbsp;&nbsp;public static final Geometry WKB2Geometry(String wkb) throws Exception {

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;// convert the String of hex values to a byte[]

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;byte[] wkbBytes = new byte[wkb.length()/2];

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;for (int i = 0; i &lt; wkbBytes.length; i++) {

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;wkbBytes[i] = (byte) ((Byte.decode(&quot;0x&quot; +

wkb.charAt(i*2)).byteValue() &lt;&lt; 4) |

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;(Byte.decode(&quot;0x&quot; +

wkb.charAt(i*2+1)).byteValue()));

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;}

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;// convert the byte[] to a JTS Geometry object

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;JTSFactory factory = new JTSFactory();

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;WKBParser parser = new WKBParser(factory);

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;parser.parseData(wkbBytes, 42102);

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ArrayList geoms = factory.getGeometries();

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;if (geoms.size() &gt; 0) {

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;return (Geometry)geoms.get(0);

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;}

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;else {

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;if (geoms.size() &gt; 1) {

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;throw new Exception(&quot;Found more than one Geometry in WKB

representation&quot;

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;}

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;throw new Exception(&quot;Could not parse WKB representations -

found no Geometries&quot;

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;}

&nbsp;&nbsp;&nbsp;&nbsp;}

codehaus
April 10, 2015, 3:28 PM

CodeHaus Comment From: cholmes - Time: Mon, 19 Jan 2004 16:00:25 -0600
---------------------
probably won&#39;t get this in for b2. I swear I&#39;ll do it soon!

codehaus
April 10, 2015, 3:28 PM

CodeHaus Comment From: cholmes - Time: Tue, 21 Sep 2004 14:47:10 -0500
---------------------
Fixed by some great work by Andrea.

codehaus
April 10, 2015, 3:28 PM

CodeHaus Comment From: - Time: Thu, 26 May 2005 23:26:48 -0500
---------------------
<a href="http://www.sneerzine.com/">http://www.sneerzine.com/</a> &lt;a href=&quot;<a href="http://www.sneerzine.com/">http://www.sneerzine.com/</a>&quot;&gt;internet casinos&lt;/a&gt; thanks

Assignee

Unassigned

Reporter

codehaus

Triage

None

Components

Priority

Medium
Configure