1

I have a MySQL table with 2 columns, id, a unique geometry id, and GeoJSON stored as a varchar, eg, 

{"type": "Point", "coordinates": [116.4, 45.2, 11.1]}

{"type":"Polygon","coordinates":[[[-91.23046875,45.460130637921],[-79.8046875,49.837982453085],[-69.08203125,43.452918893555],[-88.2421875,32.694865977875],[-91.23046875,45.460130637921]]]}

{"type":"LineString","coordinates":[[-122.2963205,37.5359200],[-122.2975821,37.5370150]]}

{"type":"MultiPolygon","coordinates":[[[[-122.3035793,37.5391205],
[-122.3026703,37.5376882],[-122.3026739,37.5376868],[-122.3035843,37.5391212],[-122.3035326,37.5391600],[-122.3035298,37.5391576],[-122.3035793,37.5391205],[-122.3035793,37.5391205]]],[[[-122.3026703,37.5376882],[-122.3026703,37.5376882],[-122.3026703,37.5376882],[-122.3026703,37.5376882],[-122.3026703,37.5376882]]]]}

I wish to store this data in PostGIS, and then I will run queries to check if a given lat/lon is in any of the geometries stored in the DB or the distances to them, etc.

If I use the geometry datatype I will have to specify the type as point, polygon etc, but I want all types in one column.

Improve this question
5
  • 3
    Geometries in PostGIS can also be defined to be of a general type "geometry".
    – user30184
    Apr 27, 2015 at 5:19
  • I have a vague recollection that key word geometry/geography type and hstore type could be very useful for you. i'm not sure if psotgis support json column queries (select from x where json.key like 'something' ) but i recall that hstore should be "easy" solution for it
    – simpleuser001
    Apr 27, 2015 at 6:52
  • What version of Postgres/Postgis. There is a native binary JSON type, JSONB, since 9.1, that allows for indexing of JSON. You still might be better splitting the JSON up and inserting as real geometry types. Could you clarify what type of queries you will be running.
    – John Powell
    Apr 27, 2015 at 7:08
  • yes the general type "geometry" helped me for that I added 2 extensions 1) postgis 2) postgis_topology Also with every geoJson object I was bound to add this key_value pairs "crs":{"type":"name","properties":{"name":"EPSG:4326"}
    – sidd.k
    Apr 27, 2015 at 14:02
  • If you have mixed srid in table you can use ST_SRID (postgis.net/docs/ST_SRID.html) to get correct srid for feature.
    – simpleuser001
    Apr 28, 2015 at 6:54

1 Answer 1

Reset to default
1

You can define your geometry column as a GeometryCollection. Then you can add whatever geometry type you want in each row.

http://postgis.net/docs/using_postgis_dbmanagement.html#OpenGISWKBWKT

CREATE TABLE foo (

...

...

geom geometry(GeometryCollection,4326)

)

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.