[GIS] Use Postgis to convert wkb_geometry data type to geom datatype

geometrypostgispostgresqlsqlwell-known-binary

How would I write an ALTER statement or otherwise to convert an existing imported data column from "wkb_geometry geometry(LineString,4326)" to a geom data type?

I ask because I've moved to importing my data from geojson instead of shapefiles due to column length issues with shapefiles and now I've created the new problem for myself of the data type not being geom as default.

Best Answer

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. That is the purpose of a tool loke ogr2ogr to convert between different formats.

Related Solutions

[GIS] Fastest way to convert shapefile to postgis in java

I am not a Java expert but I do know that Java supports subprocesses, so you could do the sort of thing I'd do in Python as follows.

Write a simple loop to iterate over your shapefiles
For each shapefile, spawn a subprocess to call ogr2ogr to dump the shapefile in one go into PostGIS without having to create a dump file first
Rinse and repeat

GDAL/OGR has Java bindings so you can use them for other geoprocessing tasks too. I know it's not GeoTools but ogr2ogr is such a wonderful Swiss-Army-Knife of a tool that no GIS Professional should be without it!

EDIT:
You don't have to install FWToools to get ogr2ogr, you can get it here

PostGIS – Calculate LineString Length from WGS84 After Transforming from OSGB 36

The length functions work differently with 3D linestring geometries:

ST_Length - returns 2D distances for geometry types, and oddly 3D distances for geography types (but not in this question)
ST_Length_Spheroid - returns 3D distances for geometry types

Your example is in 3D, so it will calculate the 3D length with ST_Length_Spheroid and the 2D length with ST_Length on a geometry.

However, if you always want 2D lengths, you can force the geometry to a 2D linestring using either ST_Force2D or ST_Force_2D function (the name change in PostGIS 2.1).

SELECT ST_Length(geom) AS length_27700,
  ST_Length_Spheroid(ST_Transform(ST_Force2d(geom), 4326), spheroid) AS length_spheroid_2d,
  ST_Length_Spheroid(ST_Transform(geom, 4326), spheroid) AS length_spheroid_3d
FROM (
  SELECT 'SRID=27700;MULTILINESTRING((
      423216.279 574665.249 0,
      423206.315 574708.077 44.649,
      423158.458 574896.911 242.525,
      423132.406 574993.061 344))'::geometry AS geom,
    'SPHEROID["WGS 84",6378137,298.257223563]'::spheroid
) AS f;

  length_27700   | length_spheroid_2d | length_spheroid_3d
-----------------+--------------------+--------------------
 338.39264086563 |   338.515774984236 |   482.622196178051

Best Answer

Related Solutions

[GIS] Fastest way to convert shapefile to postgis in java

PostGIS – Calculate LineString Length from WGS84 After Transforming from OSGB 36

Related Question