The Stack Overflow podcast is back! Listen to an interview with our new CEO.
17

Generally speaking, this is called hex-encoded WKB. This specific example is the extended version, called EWKB, since it has SRID=4326 as found by E6100000. WKB can be viewed in a few forms. The hex-encoded string representation is the most common, which if it is actually text can be converted using a simple ::geometry cast: SELECT ST_AsText(wkb_geometry), ...


10

ST_GeomFromWKB() takes the a WKB as input data type bytea. Not sure why your example uses the old escape string syntax for the string literal. Maybe because the examples in the manual still do so, too. (The manual has finally been updated. Postgres 9.3 or later showa modern notation.) It's not wrong, just unnecessarily noisy. Unless you are running a very ...


10

Use ST_GeomFromGeoJSON. SELECT ST_GeomFromGeoJSON('{"type":"MultiPolygon","coordinates":[[[[-125045.48351212002,4577567.588141698],[-124816.19981552364,4577552.93014355],[-124765.99472517562,4577419.175847012],[-124842.47121534991,4577392.905406596],[-125045.48351212002,4577567.588141698]]]]}'); Returns: ...


9

It looks like a new and better supported JS WKB parsing library has since appeared. https://github.com/cschwarz/wkx I've been able to use it to convert WKB directly from postgres into JS objects that can be mapped in the browser. You'll need to include https://github.com/cschwarz/wkx/blob/master/dist/wkx.js in your webpage for this to work. // Required ...


9

You are almost there. You just need to call the ExportToWkb function. import ogr # Get the driver driver = ogr.GetDriverByName('ESRI Shapefile') # Open a shapefile shapefileName = "D:/temp/myshapefile.shp" dataset = driver.Open(shapefileName, 0) layer = dataset.GetLayer() for index in xrange(layer.GetFeatureCount()): feature = layer.GetFeature(index) ...


9

Internally, PostGIS stores geometries in a binary specification, but it is queried and viewed outside as a hex-encoded string. There are two popular variations of well-known binary (WKB): EWKB (via ST_AsEWKB) - an extended WKB specification designed by PostGIS. OGC WKB (via ST_AsBinary) - specified by the OGC and ISO. For a while it was 2D-only, but later ...


8

Within the database, geometries are stored on disk in a format only used by the PostGIS program. In order for external programs to insert and retrieve useful geometries, they need to be converted into a format that other applications can understand. Fortunately, PostGIS supports emitting and consuming geometries in a large number of formats: from ...


7

The only solution pure javascript solution I've found so far (and I did not try) is https://github.com/thejefflarson/wkb.js. It's only an incomplete WKB parser (it converts WKB to a js object you can transform to WKT) An alternative way to wkb on javascript side can be the experimental twkb (not a standard at the moment) http://blog.jordogskog.no/2013/05/05/...


7

Shapely is based on GEOS, which natively reads/writes EWKB. The Z-dimension support of EWKB (different from ISO) is well supported by GEOS/Shapely, but getting/setting SRIDs is a hidden feature. Here's a quick demo: from shapely import geos, wkb, wkt # Show EWKB p = wkt.loads('POINT Z(1 2 3)') print(p.wkb_hex) # This is little endian EWKB with just PointZ ...


6

You'll want to use ST_AsBinary(geom) to convert your geometry from the PostGIS internal format to WKB that you can read with ogr: cur.execute('SELECT ST_AsBinary(geom) FROM mytable LIMIT 1') result = cur.fetchone() In Postgres terms, your result is a bytea. The psycpopg2 library will map this to a memoryview Python type: >>>> type(result[0]) &...


6

There are two things going on here. Pg uses the text I/O functions for datatypes by default, and even if you explicitly request bytea, the default protocol still exchanges bytea as text using the bytea_output mode. PostgreSQL clients use the text-mode protocol by default when talking to PostgreSQL. When you SELECT a geometry column, PostgreSQL uses the text ...


6

Use ST_GeomFromGeoJSON select ST_GeomFromGeoJSON('{"type":"MultiPolygon","coordinates":[[[[-125045.48351212002,4577567.588141698],[-124816.19981552364,4577552.93014355],[-124765.99472517562,4577419.175847012],[-124842.47121534991,4577392.905406596],[-125045.48351212002,4577567.588141698]]]]}') Result: "...


6

Your GeoJSON sample lacks information about the coordinate reference system, although you say it is SRID 900913. This is so-called Google Mercator or Web Mercator, which has been superseded by EPSG:3857, which I will use in this example. An important question is whether you really want Well-Known Binary, which does not include an SRID, or Extended Well-Known ...


6

The types geometry and geography are PostGIS data types, whereas point, line, path, etc. are native PostgreSQL data types. The native types offer very little functionality compared to the PostGIS types, and have relatively poor support by client applications. On the other hand, the native storage types use less storage space and may be faster for the few ...


6

(MULTI)CURVES are not part of the OGC Simple Feature Specification for WKT/WKB representations (read the same document, chapters 7 & 8; p. 51 ff). If I'm not mistaken, Oracle's spatial datatype references and representations follow the ISO/IEC 13249-3 specification, which does define a multitude of complex geometry types (it's proprietary, so I can't ...


5

You can use MapScript (part of MapServer) to make images from any sort of geometry. With (e.g.) MapScript for PHP, you could build a geometry from a WKT string with ShapeObj ms_shapeObjFromWkt(string wkt), make the style look good, then render an image object with imageObj draw() (see an example). There are different flavours of Mapscript, including for ....


5

That output is hex-encoded EWKB, or extended Well-known binary, with a specification provided here. This example is identical to ISO WKB, since it does not have an SRID or use dimensions beyond 2D (such as Z, M, or ZM). The formats are different otherwise. You can decode geometry data (i.e. longitude and latitude) from WKB using a wide range of tools ...


5

You can skip the CSV step and just go straight to shapefile using pgsql2shp, which is installed a part of PostGIS. You could also use ogr2ogr.


5

It is a string, so it can't substitute the values from your columns. You have to concatenate the string: UPDATE locations SET wkb_geometry = st_geometryfromtext('POINT('|| easting ||' '|| northing ||')',27700);


4

No offense, but, actually, the conclusions of your co-worker about how something he thinks should work doesn't make it the "official way" it should work. For that, we refer to the OGC specifications. You can download the OpenGIS Simple Features Specification for SQL 1.1 from the OGC website (a bit older) or even better, the newer OpenGIS Implementation ...


4

You already have the geom type. wkb_geometry is just the name of the column. I see it is a confusing naming since thebstorage isn't wkb but a internal PostGIS format that is quite similar to wkb. I guess you used ogr2ogr to import since I think you get that naming then. What you have as input to such a tool will not affect what you get on the output side. ...


4

If you run, using your original geometry: SELECT ST_AsText( ST_GeomFromEWKT('010600002031BF0D000100000001030000000100000004000000CE001744EA0212414BB26942EFE95641DD9AF00E49FF1441706072C152EC56413F53AF6BF9191441242DF04F7EC85641CE001744EA0212414BB26942EFE95641' )); you will see: MULTIPOLYGON(((295098.566494 6006717.0377012,344018....


4

Yes, STAsBinary() will return WKB. https://msdn.microsoft.com/en-us/library/bb933912.aspx


4

As nunatak points out, you're not correctly substituting your values within the string. However, you can avoid string substitution altogether by using http://www.postgis.net/docs/ST_MakePoint.html e.g. UPDATE locations SET wkb_geometry = ST_SetSRID(ST_MakePoint(locations.easting,locations.northing),27700); Note from the postgis docs ST_MakePoint while ...


4

The 2.5D types has such values: wkbPoint25D = -2147483647, /* 2.5D extensions as per 99-402 */ wkbLineString25D = -2147483646, wkbPolygon25D = -2147483645, wkbMultiPoint25D = -2147483644, wkbMultiLineString25D = -2147483643, wkbMultiPolygon25D = -2147483642, wkbGeometryCollection25D = -2147483641 To strip 2.5D from geometry you can use: ogr.GT_Flatten(...


4

It is not clear to me whether you are looking specifically for information on loading point clouds, or geometries in general. GEOS may not be the ideal tool for manipulating point clouds, but you can certainly transport a subset as point geometries. My example below uses a polygon, but the general strategy is geometry-agnostic. (I hadn't tried this before,...


3

The fact that there is an SRID number (4326 in your case) coming after the type number needs to be flagged, since SRID's are an optional component of EWKB. The number you are seeing is composed of the type on the bottom byte and the "has an SRID" mask on the upper byte. https://github.com/postgis/postgis/blob/svn-trunk/liblwgeom/liblwgeom.h.in#L84


3

As mentioned by ThomasG77 I have been playing with binary data in this "twkb" format. you can see it in action here (a websocket example) or here, a php implementation. If you want to study the parsing check the file twkb.js. It is a little cleaner in the twkb_node example I think. In this blog post you can find link to the source code of the PostGIS ...


3

Another possibility might be to use TopoJSON instead of plain GeoJSON: TopoJSON is an extension of GeoJSON that encodes topology. Rather than representing geometries discretely, geometries in TopoJSON files are stitched together from shared line segments called arcs. TopoJSON eliminates redundancy, offering much more compact representations of ...


3

Basically my co-worker answered my main questions, so I'm punching them in here for the sake of answering the thread. Hopefully a few people will up-vote it, giving it some added credibility. Q1: Is the ESRI Line Geometry as WKB really invalid? [Yes.] The wkb says there is 9 points in the line, and then the parser should read 9 points. MS (rightfully) ...


Only top voted, non community-wiki answers of a minimum length are eligible