Data type for mixed representation The 2019 Stack Overflow Developer Survey Results Are InWhat coordinate system should be used to store geography data for celestial coordinates?Function get_partition(geometry, unknown) does not exist - Nominatim tiger data importGet default values from PostGIS DB in QGIS 2.4 editorImporting MultiPolygon into PostGIS?How do I intersect a point with a shape?Force ST_collect to return only a GeometryCollectionGet field name of shapefile geometry for using GeoDjango LayerMappingPostGIS and ArcSDE mixed environmentHow to handle distance in postgis using ST_DWithin'UPDATE 0' results for ST_Within query - locating points within polygons
Worn-tile Scrabble
How to translate "being like"?
If I score a critical hit on an 18 or higher, what are my chances of getting a critical hit if I roll 3d20?
Why didn't the Event Horizon Telescope team mention Sagittarius A*?
Old scifi movie from the 50s or 60s with men in solid red uniforms who interrogate a spy from the past
How do PCB vias affect signal quality?
How to notate time signature switching consistently every measure
Keeping a retro style to sci-fi spaceships?
Button changing its text & action. Good or terrible?
How to type a long/em dash `—`
Why couldn't they take pictures of a closer black hole?
Can withdrawing asylum be illegal?
What was the last CPU that did not have the x87 floating-point unit built in?
How come people say “Would of”?
How to add class in ko template in magento2
Why can't wing-mounted spoilers be used to steepen approaches?
Short story: man watches girlfriend's spaceship entering a 'black hole' (?) forever
Is it ethical to upload a automatically generated paper to a non peer-reviewed site as part of a larger research?
What is preventing me from simply constructing a hash that's lower than the current target?
How do you keep chess fun when your opponent constantly beats you?
Can we generate random numbers using irrational numbers like π and e?
Why is this code so slow?
Why are there uneven bright areas in this photo of black hole?
Accepted by European university, rejected by all American ones I applied to? Possible reasons?
Data type for mixed representation
The 2019 Stack Overflow Developer Survey Results Are InWhat coordinate system should be used to store geography data for celestial coordinates?Function get_partition(geometry, unknown) does not exist - Nominatim tiger data importGet default values from PostGIS DB in QGIS 2.4 editorImporting MultiPolygon into PostGIS?How do I intersect a point with a shape?Force ST_collect to return only a GeometryCollectionGet field name of shapefile geometry for using GeoDjango LayerMappingPostGIS and ArcSDE mixed environmentHow to handle distance in postgis using ST_DWithin'UPDATE 0' results for ST_Within query - locating points within polygons
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;
1
The following is a JSON representation of an large array of planar spatial surfaces.
"type":"Feature",
"geometry":
"type":"MultiPolygon",
"coordinates":
[[[[17.7529,-31.146],[17.9742,-30.9097],[17.9783,-30.7836],[18.0728,-30.7795],[18.1533,-30.8411],[18.245,-30.8067],[18.3456,-30.6569],[18.3967,-30.5128],[18.5503,-30.4289],[18.6436,-30.5436],[18.7428,-30.5625],[18.7678,-30.6539],[18.8481,-30.7147],[18.9386,-30.7242],[18.9667,-30.9292],[18.8858,-31.03],[18.9364,-31.1072],[18.9269,-31.2161],[19.0139,-31.3694],[19.0639,-31.5083],[19.0869,-31.7522],[19.0311,-31.8328],[19.0883,-31.8969],[19.293,-31.9775],[19.3217,-32.0317],[19.4822,-32.0814],[19.4961,-32.2456],[19.3989,-32.2922],[19.4917,-32.4711],[19.4728,-32.5836],[19.5953,-32.6236],[19.605,-32.4908],[19.7944,-32.387],[19.9222,-32.3739],[20.0386,-32.2497],[20.1644,-32.1758],[20.2178,-32.2306],[20.1494,-32.3728],[20.0647,-32.4389],[20.1761,-32.7578],[20.2717,-32.7917],[20.4211,-32.9369],[20.5564,-32.8844],[20.5964,-32.9367],[20.7181,-32.9283],[20.8206,-32.8108],[20.7953,-32.6967],[20.9678,-32.6369],[21.1611,-32.613],[21.1922,-32.4592],[21.3231,-32.3736],[21.5469,-32.305],[21.5375,-32.2236],[21.7236,-32.1981],[21.8506,-32.2619],[21.9986,-32.1881],[22.0019,-32.0978],[22.1233,-32.05],[22.0636,-31.9203],[22.1653,-31.8786],[22.1711,-31.7758],[22.2914,-31.5703],[22.5922,-31.725],[22.565,-31.8005],[22.7233,-31.8022],[23.0464,-31.8853],[23.0697,-31.9767],[23.2139,-31.8681],[23.2664,-31.7425],[23.4008,-31.6814],[23.5097,-31.6994],[23.5847,-31.6544],[23.7258,-31.6589],[23.8261,-31.7256],[23.8567,-31.8233],[24.0206,-31.7281],[24.1469,-31.79],[24.1522,-31.895],[24.205,-31.9472],[24.0872,-32.0906],[24.0719,-32.1556],[23.8736,-32.2461],[23.7519,-32.1767],[23.6539,-32.2353],[23.6458,-32.3336],[23.4325,-32.3283],[23.2081,-32.4247],[23.2678,-32.5342],[23.3633,-32.8264],[23.1781,-32.7894],[23.0778,-32.8781],[22.9381,-32.9425],[22.9364,-33.0914],[22.8806,-33.1372],[22.7633,-33.3169],[22.7494,-33.4017],[22.8972,-33.4145],[23.0394,-33.3833],[23.2925,-33.3903],[23.3236,-33.432],[23.48,-33.4344],[23.5747,-33.4886],[23.655,-33.6175],[23.4125,-33.7133],[23.465,-33.7967],[23.6867,-33.8722],[23.6395,-33.9821],[23.449,-34.0051],[23.3649,-34.1051],[23.2251,-34.0871],[22.959,-34.0887],[22.9149,-34.0646],[22.564,-33.9962],[22.4851,-34.0526],[22.2371,-34.0576],[22.1232,-34.1165],[22.1135,-34.2049],[21.931,-34.2451],[21.8696,-34.364],[21.6954,-34.3957],[21.5368,-34.3568],[21.4446,-34.3682],[21.3051,-34.441],[21.0015,-34.3649],[20.8537,-34.3976],[20.8474,-34.4751],[20.6626,-34.4493],[20.5338,-34.4701],[20.4007,-34.5724],[20.0829,-34.7332],[19.9904,-34.8351],[19.8751,-34.7668],[19.6329,-34.7551],[19.4485,-34.6551],[19.354,-34.4774],[19.2713,-34.4093],[19.1226,-34.4185],[19.0376,-34.3412],[18.911,-34.3574],[18.8132,-34.1007],[18.7126,-34.0721],[18.4946,-34.0988],[18.3804,-34.2624],[18.3304,-34.024],[18.3976,-33.8993],[18.4854,-33.8415],[18.4376,-33.6929],[18.3154,-33.5096],[18.0974,-33.2301],[17.9696,-33.1376],[17.9915,-32.9965],[17.871,-32.991],[17.8957,-32.7429],[17.9796,-32.7268],[18.129,-32.7774],[18.2479,-32.6774],[18.3349,-32.4587],[18.3135,-32.3171],[18.3457,-32.281],[18.2257,-31.7507],[18.1165,-31.5829],[17.899,-31.351],[17.879,-31.2801],[17.7529,-31.146]]],[[[37.9069,-46.8996],[37.8564,-46.9683],[37.657,-46.9744],[37.5803,-46.9364],[37.6894,-46.8239],[37.8047,-46.8372],[37.9069,-46.8996]]]]
,
"properties":
"name":"Western Cape",
"id":"ZA-WC",
"CNTRY":"South Africa",
"TYPE":"Province"
,
"id":"ZA-WC"
,
"type":"Feature",
"geometry":
"type":"Polygon",
"coordinates":
[[[-2.1512,49.2607],[-2.1353,49.2535],[-2.1049,49.2506],[-2.1013,49.2558],[-2.0913,49.2536],[-2.0801,49.2393],[-2.0731,49.2377],[-2.0529,49.2423],[-2.0414,49.2363],[-2.0253,49.2321],[-2.024,49.2271],[-2.03,49.2163],[-2.0179,49.2003],[-2.0283,49.1982],[-2.033,49.182],[-2.0277,49.1679],[-2.0332,49.1644],[-2.054,49.17],[-2.0782,49.1635],[-2.0917,49.1751],[-2.0985,49.1779],[-2.111,49.1764],[-2.1186,49.189],[-2.1311,49.1948],[-2.1421,49.1968],[-2.1595,49.1959],[-2.1692,49.1891],[-2.1695,49.1789],[-2.175,49.1717],[-2.1887,49.1765],[-2.19,49.1835],[-2.2018,49.185],[-2.204,49.1788],[-2.2234,49.1748],[-2.2435,49.1818],[-2.2309,49.1892],[-2.2233,49.2044],[-2.2307,49.2255],[-2.2421,49.2388],[-2.2535,49.2476],[-2.2538,49.2528],[-2.2425,49.259],[-2.211,49.2549],[-2.1974,49.2495],[-2.1826,49.2495],[-2.1704,49.2539],[-2.1653,49.259],[-2.1512,49.2607]]]
,
"properties":
"name":"Jersey",
"id":"JE",
"TYPE":"Jersey",
"CNTRY":"Jersey"
,
"id":"JE"
,
While this documentation describes representations POLYGON and MULTIPOLYGON, how should this table should be created as the object will have coordinates, assuming using Rails' PostGIS adapter
create_table :my_spatial_table do |t|
t.st_polygon :the_shape, , srid: -1
[and/or]
t.multi_polygon :the_multi_shape, srid: -1
t.string :name
t.string :id
t.string :country
t.string :type
t.boolean :multi
end
Intuitively, I would go with the multi_polygon allowing for records with a single polygon defined. However this appears to be at odds with the examples of text representations (logically, 2 parenthesis for polygon, 3 for multipolygon) the documentation; it appears postgresql is expecting a specific format. Naturally, this is intrinsincly linked to the import of the data.
Assuming this data is transformed into a CSV file, how should the table be created
- with 1 MULTIPOLYGON attribute , or,
- with 2 attributes, 1 MULTIPOLYGON and the other POLYGON with a boolean descriptor allowing queries to focus on one attribute of the other?
And how should the data be represented for proper digestion by PostgreSQL in the import process (parenthesis in lieu of square brackets and in relevant numbers, I assume), particluarly if one choose the single MULTIPOLYGON datatype ?
postgis postgresql csv ruby
edited Apr 6 at 12:22
Vince
14.8k32850
asked Apr 6 at 11:52
JeromeJerome
13016
add a comment |
1
The following is a JSON representation of an large array of planar spatial surfaces.
"type":"Feature",
"geometry":
"type":"MultiPolygon",
"coordinates":
[[[[17.7529,-31.146],[17.9742,-30.9097],[17.9783,-30.7836],[18.0728,-30.7795],[18.1533,-30.8411],[18.245,-30.8067],[18.3456,-30.6569],[18.3967,-30.5128],[18.5503,-30.4289],[18.6436,-30.5436],[18.7428,-30.5625],[18.7678,-30.6539],[18.8481,-30.7147],[18.9386,-30.7242],[18.9667,-30.9292],[18.8858,-31.03],[18.9364,-31.1072],[18.9269,-31.2161],[19.0139,-31.3694],[19.0639,-31.5083],[19.0869,-31.7522],[19.0311,-31.8328],[19.0883,-31.8969],[19.293,-31.9775],[19.3217,-32.0317],[19.4822,-32.0814],[19.4961,-32.2456],[19.3989,-32.2922],[19.4917,-32.4711],[19.4728,-32.5836],[19.5953,-32.6236],[19.605,-32.4908],[19.7944,-32.387],[19.9222,-32.3739],[20.0386,-32.2497],[20.1644,-32.1758],[20.2178,-32.2306],[20.1494,-32.3728],[20.0647,-32.4389],[20.1761,-32.7578],[20.2717,-32.7917],[20.4211,-32.9369],[20.5564,-32.8844],[20.5964,-32.9367],[20.7181,-32.9283],[20.8206,-32.8108],[20.7953,-32.6967],[20.9678,-32.6369],[21.1611,-32.613],[21.1922,-32.4592],[21.3231,-32.3736],[21.5469,-32.305],[21.5375,-32.2236],[21.7236,-32.1981],[21.8506,-32.2619],[21.9986,-32.1881],[22.0019,-32.0978],[22.1233,-32.05],[22.0636,-31.9203],[22.1653,-31.8786],[22.1711,-31.7758],[22.2914,-31.5703],[22.5922,-31.725],[22.565,-31.8005],[22.7233,-31.8022],[23.0464,-31.8853],[23.0697,-31.9767],[23.2139,-31.8681],[23.2664,-31.7425],[23.4008,-31.6814],[23.5097,-31.6994],[23.5847,-31.6544],[23.7258,-31.6589],[23.8261,-31.7256],[23.8567,-31.8233],[24.0206,-31.7281],[24.1469,-31.79],[24.1522,-31.895],[24.205,-31.9472],[24.0872,-32.0906],[24.0719,-32.1556],[23.8736,-32.2461],[23.7519,-32.1767],[23.6539,-32.2353],[23.6458,-32.3336],[23.4325,-32.3283],[23.2081,-32.4247],[23.2678,-32.5342],[23.3633,-32.8264],[23.1781,-32.7894],[23.0778,-32.8781],[22.9381,-32.9425],[22.9364,-33.0914],[22.8806,-33.1372],[22.7633,-33.3169],[22.7494,-33.4017],[22.8972,-33.4145],[23.0394,-33.3833],[23.2925,-33.3903],[23.3236,-33.432],[23.48,-33.4344],[23.5747,-33.4886],[23.655,-33.6175],[23.4125,-33.7133],[23.465,-33.7967],[23.6867,-33.8722],[23.6395,-33.9821],[23.449,-34.0051],[23.3649,-34.1051],[23.2251,-34.0871],[22.959,-34.0887],[22.9149,-34.0646],[22.564,-33.9962],[22.4851,-34.0526],[22.2371,-34.0576],[22.1232,-34.1165],[22.1135,-34.2049],[21.931,-34.2451],[21.8696,-34.364],[21.6954,-34.3957],[21.5368,-34.3568],[21.4446,-34.3682],[21.3051,-34.441],[21.0015,-34.3649],[20.8537,-34.3976],[20.8474,-34.4751],[20.6626,-34.4493],[20.5338,-34.4701],[20.4007,-34.5724],[20.0829,-34.7332],[19.9904,-34.8351],[19.8751,-34.7668],[19.6329,-34.7551],[19.4485,-34.6551],[19.354,-34.4774],[19.2713,-34.4093],[19.1226,-34.4185],[19.0376,-34.3412],[18.911,-34.3574],[18.8132,-34.1007],[18.7126,-34.0721],[18.4946,-34.0988],[18.3804,-34.2624],[18.3304,-34.024],[18.3976,-33.8993],[18.4854,-33.8415],[18.4376,-33.6929],[18.3154,-33.5096],[18.0974,-33.2301],[17.9696,-33.1376],[17.9915,-32.9965],[17.871,-32.991],[17.8957,-32.7429],[17.9796,-32.7268],[18.129,-32.7774],[18.2479,-32.6774],[18.3349,-32.4587],[18.3135,-32.3171],[18.3457,-32.281],[18.2257,-31.7507],[18.1165,-31.5829],[17.899,-31.351],[17.879,-31.2801],[17.7529,-31.146]]],[[[37.9069,-46.8996],[37.8564,-46.9683],[37.657,-46.9744],[37.5803,-46.9364],[37.6894,-46.8239],[37.8047,-46.8372],[37.9069,-46.8996]]]]
,
"properties":
"name":"Western Cape",
"id":"ZA-WC",
"CNTRY":"South Africa",
"TYPE":"Province"
,
"id":"ZA-WC"
,
"type":"Feature",
"geometry":
"type":"Polygon",
"coordinates":
[[[-2.1512,49.2607],[-2.1353,49.2535],[-2.1049,49.2506],[-2.1013,49.2558],[-2.0913,49.2536],[-2.0801,49.2393],[-2.0731,49.2377],[-2.0529,49.2423],[-2.0414,49.2363],[-2.0253,49.2321],[-2.024,49.2271],[-2.03,49.2163],[-2.0179,49.2003],[-2.0283,49.1982],[-2.033,49.182],[-2.0277,49.1679],[-2.0332,49.1644],[-2.054,49.17],[-2.0782,49.1635],[-2.0917,49.1751],[-2.0985,49.1779],[-2.111,49.1764],[-2.1186,49.189],[-2.1311,49.1948],[-2.1421,49.1968],[-2.1595,49.1959],[-2.1692,49.1891],[-2.1695,49.1789],[-2.175,49.1717],[-2.1887,49.1765],[-2.19,49.1835],[-2.2018,49.185],[-2.204,49.1788],[-2.2234,49.1748],[-2.2435,49.1818],[-2.2309,49.1892],[-2.2233,49.2044],[-2.2307,49.2255],[-2.2421,49.2388],[-2.2535,49.2476],[-2.2538,49.2528],[-2.2425,49.259],[-2.211,49.2549],[-2.1974,49.2495],[-2.1826,49.2495],[-2.1704,49.2539],[-2.1653,49.259],[-2.1512,49.2607]]]
,
"properties":
"name":"Jersey",
"id":"JE",
"TYPE":"Jersey",
"CNTRY":"Jersey"
,
"id":"JE"
,
While this documentation describes representations POLYGON and MULTIPOLYGON, how should this table should be created as the object will have coordinates, assuming using Rails' PostGIS adapter
create_table :my_spatial_table do |t|
t.st_polygon :the_shape, , srid: -1
[and/or]
t.multi_polygon :the_multi_shape, srid: -1
t.string :name
t.string :id
t.string :country
t.string :type
t.boolean :multi
end
Intuitively, I would go with the multi_polygon allowing for records with a single polygon defined. However this appears to be at odds with the examples of text representations (logically, 2 parenthesis for polygon, 3 for multipolygon) the documentation; it appears postgresql is expecting a specific format. Naturally, this is intrinsincly linked to the import of the data.
Assuming this data is transformed into a CSV file, how should the table be created
- with 1 MULTIPOLYGON attribute , or,
- with 2 attributes, 1 MULTIPOLYGON and the other POLYGON with a boolean descriptor allowing queries to focus on one attribute of the other?
And how should the data be represented for proper digestion by PostgreSQL in the import process (parenthesis in lieu of square brackets and in relevant numbers, I assume), particluarly if one choose the single MULTIPOLYGON datatype ?
postgis postgresql csv ruby
edited Apr 6 at 12:22
Vince
14.8k32850
asked Apr 6 at 11:52
JeromeJerome
13016
Create geometry column in PostGIS as multipolygon and cast polygons of the source data into multipolygons before inserting them. For example ogr2ogr gdal.org/ogr2ogr.html can do that automatically with-nlt PROMOTE_TO:MULTI. Another option is to create the geometry column in PostGIS as general "geometry" when it will accept any kind of geometries.
– user30184
Apr 6 at 17:58
should the arrays be cast with parenthesis or will square brackets do?
– Jerome
Apr 6 at 18:29
If you want to manipulate the original GeoJSON data you can find an example from here tools.ietf.org/html/rfc7946#appendix-A.6.
– user30184
Apr 6 at 18:54
add a comment |
1
1
1
The following is a JSON representation of an large array of planar spatial surfaces.
"type":"Feature",
"geometry":
"type":"MultiPolygon",
"coordinates":
[[[[17.7529,-31.146],[17.9742,-30.9097],[17.9783,-30.7836],[18.0728,-30.7795],[18.1533,-30.8411],[18.245,-30.8067],[18.3456,-30.6569],[18.3967,-30.5128],[18.5503,-30.4289],[18.6436,-30.5436],[18.7428,-30.5625],[18.7678,-30.6539],[18.8481,-30.7147],[18.9386,-30.7242],[18.9667,-30.9292],[18.8858,-31.03],[18.9364,-31.1072],[18.9269,-31.2161],[19.0139,-31.3694],[19.0639,-31.5083],[19.0869,-31.7522],[19.0311,-31.8328],[19.0883,-31.8969],[19.293,-31.9775],[19.3217,-32.0317],[19.4822,-32.0814],[19.4961,-32.2456],[19.3989,-32.2922],[19.4917,-32.4711],[19.4728,-32.5836],[19.5953,-32.6236],[19.605,-32.4908],[19.7944,-32.387],[19.9222,-32.3739],[20.0386,-32.2497],[20.1644,-32.1758],[20.2178,-32.2306],[20.1494,-32.3728],[20.0647,-32.4389],[20.1761,-32.7578],[20.2717,-32.7917],[20.4211,-32.9369],[20.5564,-32.8844],[20.5964,-32.9367],[20.7181,-32.9283],[20.8206,-32.8108],[20.7953,-32.6967],[20.9678,-32.6369],[21.1611,-32.613],[21.1922,-32.4592],[21.3231,-32.3736],[21.5469,-32.305],[21.5375,-32.2236],[21.7236,-32.1981],[21.8506,-32.2619],[21.9986,-32.1881],[22.0019,-32.0978],[22.1233,-32.05],[22.0636,-31.9203],[22.1653,-31.8786],[22.1711,-31.7758],[22.2914,-31.5703],[22.5922,-31.725],[22.565,-31.8005],[22.7233,-31.8022],[23.0464,-31.8853],[23.0697,-31.9767],[23.2139,-31.8681],[23.2664,-31.7425],[23.4008,-31.6814],[23.5097,-31.6994],[23.5847,-31.6544],[23.7258,-31.6589],[23.8261,-31.7256],[23.8567,-31.8233],[24.0206,-31.7281],[24.1469,-31.79],[24.1522,-31.895],[24.205,-31.9472],[24.0872,-32.0906],[24.0719,-32.1556],[23.8736,-32.2461],[23.7519,-32.1767],[23.6539,-32.2353],[23.6458,-32.3336],[23.4325,-32.3283],[23.2081,-32.4247],[23.2678,-32.5342],[23.3633,-32.8264],[23.1781,-32.7894],[23.0778,-32.8781],[22.9381,-32.9425],[22.9364,-33.0914],[22.8806,-33.1372],[22.7633,-33.3169],[22.7494,-33.4017],[22.8972,-33.4145],[23.0394,-33.3833],[23.2925,-33.3903],[23.3236,-33.432],[23.48,-33.4344],[23.5747,-33.4886],[23.655,-33.6175],[23.4125,-33.7133],[23.465,-33.7967],[23.6867,-33.8722],[23.6395,-33.9821],[23.449,-34.0051],[23.3649,-34.1051],[23.2251,-34.0871],[22.959,-34.0887],[22.9149,-34.0646],[22.564,-33.9962],[22.4851,-34.0526],[22.2371,-34.0576],[22.1232,-34.1165],[22.1135,-34.2049],[21.931,-34.2451],[21.8696,-34.364],[21.6954,-34.3957],[21.5368,-34.3568],[21.4446,-34.3682],[21.3051,-34.441],[21.0015,-34.3649],[20.8537,-34.3976],[20.8474,-34.4751],[20.6626,-34.4493],[20.5338,-34.4701],[20.4007,-34.5724],[20.0829,-34.7332],[19.9904,-34.8351],[19.8751,-34.7668],[19.6329,-34.7551],[19.4485,-34.6551],[19.354,-34.4774],[19.2713,-34.4093],[19.1226,-34.4185],[19.0376,-34.3412],[18.911,-34.3574],[18.8132,-34.1007],[18.7126,-34.0721],[18.4946,-34.0988],[18.3804,-34.2624],[18.3304,-34.024],[18.3976,-33.8993],[18.4854,-33.8415],[18.4376,-33.6929],[18.3154,-33.5096],[18.0974,-33.2301],[17.9696,-33.1376],[17.9915,-32.9965],[17.871,-32.991],[17.8957,-32.7429],[17.9796,-32.7268],[18.129,-32.7774],[18.2479,-32.6774],[18.3349,-32.4587],[18.3135,-32.3171],[18.3457,-32.281],[18.2257,-31.7507],[18.1165,-31.5829],[17.899,-31.351],[17.879,-31.2801],[17.7529,-31.146]]],[[[37.9069,-46.8996],[37.8564,-46.9683],[37.657,-46.9744],[37.5803,-46.9364],[37.6894,-46.8239],[37.8047,-46.8372],[37.9069,-46.8996]]]]
,
"properties":
"name":"Western Cape",
"id":"ZA-WC",
"CNTRY":"South Africa",
"TYPE":"Province"
,
"id":"ZA-WC"
,
"type":"Feature",
"geometry":
"type":"Polygon",
"coordinates":
[[[-2.1512,49.2607],[-2.1353,49.2535],[-2.1049,49.2506],[-2.1013,49.2558],[-2.0913,49.2536],[-2.0801,49.2393],[-2.0731,49.2377],[-2.0529,49.2423],[-2.0414,49.2363],[-2.0253,49.2321],[-2.024,49.2271],[-2.03,49.2163],[-2.0179,49.2003],[-2.0283,49.1982],[-2.033,49.182],[-2.0277,49.1679],[-2.0332,49.1644],[-2.054,49.17],[-2.0782,49.1635],[-2.0917,49.1751],[-2.0985,49.1779],[-2.111,49.1764],[-2.1186,49.189],[-2.1311,49.1948],[-2.1421,49.1968],[-2.1595,49.1959],[-2.1692,49.1891],[-2.1695,49.1789],[-2.175,49.1717],[-2.1887,49.1765],[-2.19,49.1835],[-2.2018,49.185],[-2.204,49.1788],[-2.2234,49.1748],[-2.2435,49.1818],[-2.2309,49.1892],[-2.2233,49.2044],[-2.2307,49.2255],[-2.2421,49.2388],[-2.2535,49.2476],[-2.2538,49.2528],[-2.2425,49.259],[-2.211,49.2549],[-2.1974,49.2495],[-2.1826,49.2495],[-2.1704,49.2539],[-2.1653,49.259],[-2.1512,49.2607]]]
,
"properties":
"name":"Jersey",
"id":"JE",
"TYPE":"Jersey",
"CNTRY":"Jersey"
,
"id":"JE"
,
While this documentation describes representations POLYGON and MULTIPOLYGON, how should this table should be created as the object will have coordinates, assuming using Rails' PostGIS adapter
create_table :my_spatial_table do |t|
t.st_polygon :the_shape, , srid: -1
[and/or]
t.multi_polygon :the_multi_shape, srid: -1
t.string :name
t.string :id
t.string :country
t.string :type
t.boolean :multi
end
Intuitively, I would go with the multi_polygon allowing for records with a single polygon defined. However this appears to be at odds with the examples of text representations (logically, 2 parenthesis for polygon, 3 for multipolygon) the documentation; it appears postgresql is expecting a specific format. Naturally, this is intrinsincly linked to the import of the data.
Assuming this data is transformed into a CSV file, how should the table be created
- with 1 MULTIPOLYGON attribute , or,
- with 2 attributes, 1 MULTIPOLYGON and the other POLYGON with a boolean descriptor allowing queries to focus on one attribute of the other?
And how should the data be represented for proper digestion by PostgreSQL in the import process (parenthesis in lieu of square brackets and in relevant numbers, I assume), particluarly if one choose the single MULTIPOLYGON datatype ?
postgis postgresql csv ruby
edited Apr 6 at 12:22
Vince
14.8k32850
asked Apr 6 at 11:52
JeromeJerome
13016
The following is a JSON representation of an large array of planar spatial surfaces.
"type":"Feature",
"geometry":
"type":"MultiPolygon",
"coordinates":
[[[[17.7529,-31.146],[17.9742,-30.9097],[17.9783,-30.7836],[18.0728,-30.7795],[18.1533,-30.8411],[18.245,-30.8067],[18.3456,-30.6569],[18.3967,-30.5128],[18.5503,-30.4289],[18.6436,-30.5436],[18.7428,-30.5625],[18.7678,-30.6539],[18.8481,-30.7147],[18.9386,-30.7242],[18.9667,-30.9292],[18.8858,-31.03],[18.9364,-31.1072],[18.9269,-31.2161],[19.0139,-31.3694],[19.0639,-31.5083],[19.0869,-31.7522],[19.0311,-31.8328],[19.0883,-31.8969],[19.293,-31.9775],[19.3217,-32.0317],[19.4822,-32.0814],[19.4961,-32.2456],[19.3989,-32.2922],[19.4917,-32.4711],[19.4728,-32.5836],[19.5953,-32.6236],[19.605,-32.4908],[19.7944,-32.387],[19.9222,-32.3739],[20.0386,-32.2497],[20.1644,-32.1758],[20.2178,-32.2306],[20.1494,-32.3728],[20.0647,-32.4389],[20.1761,-32.7578],[20.2717,-32.7917],[20.4211,-32.9369],[20.5564,-32.8844],[20.5964,-32.9367],[20.7181,-32.9283],[20.8206,-32.8108],[20.7953,-32.6967],[20.9678,-32.6369],[21.1611,-32.613],[21.1922,-32.4592],[21.3231,-32.3736],[21.5469,-32.305],[21.5375,-32.2236],[21.7236,-32.1981],[21.8506,-32.2619],[21.9986,-32.1881],[22.0019,-32.0978],[22.1233,-32.05],[22.0636,-31.9203],[22.1653,-31.8786],[22.1711,-31.7758],[22.2914,-31.5703],[22.5922,-31.725],[22.565,-31.8005],[22.7233,-31.8022],[23.0464,-31.8853],[23.0697,-31.9767],[23.2139,-31.8681],[23.2664,-31.7425],[23.4008,-31.6814],[23.5097,-31.6994],[23.5847,-31.6544],[23.7258,-31.6589],[23.8261,-31.7256],[23.8567,-31.8233],[24.0206,-31.7281],[24.1469,-31.79],[24.1522,-31.895],[24.205,-31.9472],[24.0872,-32.0906],[24.0719,-32.1556],[23.8736,-32.2461],[23.7519,-32.1767],[23.6539,-32.2353],[23.6458,-32.3336],[23.4325,-32.3283],[23.2081,-32.4247],[23.2678,-32.5342],[23.3633,-32.8264],[23.1781,-32.7894],[23.0778,-32.8781],[22.9381,-32.9425],[22.9364,-33.0914],[22.8806,-33.1372],[22.7633,-33.3169],[22.7494,-33.4017],[22.8972,-33.4145],[23.0394,-33.3833],[23.2925,-33.3903],[23.3236,-33.432],[23.48,-33.4344],[23.5747,-33.4886],[23.655,-33.6175],[23.4125,-33.7133],[23.465,-33.7967],[23.6867,-33.8722],[23.6395,-33.9821],[23.449,-34.0051],[23.3649,-34.1051],[23.2251,-34.0871],[22.959,-34.0887],[22.9149,-34.0646],[22.564,-33.9962],[22.4851,-34.0526],[22.2371,-34.0576],[22.1232,-34.1165],[22.1135,-34.2049],[21.931,-34.2451],[21.8696,-34.364],[21.6954,-34.3957],[21.5368,-34.3568],[21.4446,-34.3682],[21.3051,-34.441],[21.0015,-34.3649],[20.8537,-34.3976],[20.8474,-34.4751],[20.6626,-34.4493],[20.5338,-34.4701],[20.4007,-34.5724],[20.0829,-34.7332],[19.9904,-34.8351],[19.8751,-34.7668],[19.6329,-34.7551],[19.4485,-34.6551],[19.354,-34.4774],[19.2713,-34.4093],[19.1226,-34.4185],[19.0376,-34.3412],[18.911,-34.3574],[18.8132,-34.1007],[18.7126,-34.0721],[18.4946,-34.0988],[18.3804,-34.2624],[18.3304,-34.024],[18.3976,-33.8993],[18.4854,-33.8415],[18.4376,-33.6929],[18.3154,-33.5096],[18.0974,-33.2301],[17.9696,-33.1376],[17.9915,-32.9965],[17.871,-32.991],[17.8957,-32.7429],[17.9796,-32.7268],[18.129,-32.7774],[18.2479,-32.6774],[18.3349,-32.4587],[18.3135,-32.3171],[18.3457,-32.281],[18.2257,-31.7507],[18.1165,-31.5829],[17.899,-31.351],[17.879,-31.2801],[17.7529,-31.146]]],[[[37.9069,-46.8996],[37.8564,-46.9683],[37.657,-46.9744],[37.5803,-46.9364],[37.6894,-46.8239],[37.8047,-46.8372],[37.9069,-46.8996]]]]
,
"properties":
"name":"Western Cape",
"id":"ZA-WC",
"CNTRY":"South Africa",
"TYPE":"Province"
,
"id":"ZA-WC"
,
"type":"Feature",
"geometry":
"type":"Polygon",
"coordinates":
[[[-2.1512,49.2607],[-2.1353,49.2535],[-2.1049,49.2506],[-2.1013,49.2558],[-2.0913,49.2536],[-2.0801,49.2393],[-2.0731,49.2377],[-2.0529,49.2423],[-2.0414,49.2363],[-2.0253,49.2321],[-2.024,49.2271],[-2.03,49.2163],[-2.0179,49.2003],[-2.0283,49.1982],[-2.033,49.182],[-2.0277,49.1679],[-2.0332,49.1644],[-2.054,49.17],[-2.0782,49.1635],[-2.0917,49.1751],[-2.0985,49.1779],[-2.111,49.1764],[-2.1186,49.189],[-2.1311,49.1948],[-2.1421,49.1968],[-2.1595,49.1959],[-2.1692,49.1891],[-2.1695,49.1789],[-2.175,49.1717],[-2.1887,49.1765],[-2.19,49.1835],[-2.2018,49.185],[-2.204,49.1788],[-2.2234,49.1748],[-2.2435,49.1818],[-2.2309,49.1892],[-2.2233,49.2044],[-2.2307,49.2255],[-2.2421,49.2388],[-2.2535,49.2476],[-2.2538,49.2528],[-2.2425,49.259],[-2.211,49.2549],[-2.1974,49.2495],[-2.1826,49.2495],[-2.1704,49.2539],[-2.1653,49.259],[-2.1512,49.2607]]]
,
"properties":
"name":"Jersey",
"id":"JE",
"TYPE":"Jersey",
"CNTRY":"Jersey"
,
"id":"JE"
,
While this documentation describes representations POLYGON and MULTIPOLYGON, how should this table should be created as the object will have coordinates, assuming using Rails' PostGIS adapter
create_table :my_spatial_table do |t|
t.st_polygon :the_shape, , srid: -1
[and/or]
t.multi_polygon :the_multi_shape, srid: -1
t.string :name
t.string :id
t.string :country
t.string :type
t.boolean :multi
end
Intuitively, I would go with the multi_polygon allowing for records with a single polygon defined. However this appears to be at odds with the examples of text representations (logically, 2 parenthesis for polygon, 3 for multipolygon) the documentation; it appears postgresql is expecting a specific format. Naturally, this is intrinsincly linked to the import of the data.
Assuming this data is transformed into a CSV file, how should the table be created
- with 1 MULTIPOLYGON attribute , or,
- with 2 attributes, 1 MULTIPOLYGON and the other POLYGON with a boolean descriptor allowing queries to focus on one attribute of the other?
And how should the data be represented for proper digestion by PostgreSQL in the import process (parenthesis in lieu of square brackets and in relevant numbers, I assume), particluarly if one choose the single MULTIPOLYGON datatype ?
postgis postgresql csv ruby
postgis postgresql csv ruby
edited Apr 6 at 12:22
Vince
14.8k32850
asked Apr 6 at 11:52
JeromeJerome
13016
edited Apr 6 at 12:22
Vince
14.8k32850
asked Apr 6 at 11:52
JeromeJerome
13016
edited Apr 6 at 12:22
Vince
14.8k32850
edited Apr 6 at 12:22
Vince
14.8k32850
edited Apr 6 at 12:22
Vince
14.8k32850
14.8k32850
asked Apr 6 at 11:52
JeromeJerome
13016
asked Apr 6 at 11:52
JeromeJerome
13016
asked Apr 6 at 11:52
JeromeJerome
13016
13016
Create geometry column in PostGIS as multipolygon and cast polygons of the source data into multipolygons before inserting them. For example ogr2ogr gdal.org/ogr2ogr.html can do that automatically with-nlt PROMOTE_TO:MULTI. Another option is to create the geometry column in PostGIS as general "geometry" when it will accept any kind of geometries.
– user30184
Apr 6 at 17:58
should the arrays be cast with parenthesis or will square brackets do?
– Jerome
Apr 6 at 18:29
If you want to manipulate the original GeoJSON data you can find an example from here tools.ietf.org/html/rfc7946#appendix-A.6.
– user30184
Apr 6 at 18:54
add a comment |
Create geometry column in PostGIS as multipolygon and cast polygons of the source data into multipolygons before inserting them. For example ogr2ogr gdal.org/ogr2ogr.html can do that automatically with-nlt PROMOTE_TO:MULTI. Another option is to create the geometry column in PostGIS as general "geometry" when it will accept any kind of geometries.
– user30184
Apr 6 at 17:58
should the arrays be cast with parenthesis or will square brackets do?
– Jerome
Apr 6 at 18:29
If you want to manipulate the original GeoJSON data you can find an example from here tools.ietf.org/html/rfc7946#appendix-A.6.
– user30184
Apr 6 at 18:54
Create geometry column in PostGIS as multipolygon and cast polygons of the source data into multipolygons before inserting them. For example ogr2ogr gdal.org/ogr2ogr.html can do that automatically with
-nlt PROMOTE_TO:MULTI. Another option is to create the geometry column in PostGIS as general "geometry" when it will accept any kind of geometries.– user30184
Apr 6 at 17:58
Create geometry column in PostGIS as multipolygon and cast polygons of the source data into multipolygons before inserting them. For example ogr2ogr gdal.org/ogr2ogr.html can do that automatically with
-nlt PROMOTE_TO:MULTI. Another option is to create the geometry column in PostGIS as general "geometry" when it will accept any kind of geometries.– user30184
Apr 6 at 17:58
should the arrays be cast with parenthesis or will square brackets do?
– Jerome
Apr 6 at 18:29
should the arrays be cast with parenthesis or will square brackets do?
– Jerome
Apr 6 at 18:29
If you want to manipulate the original GeoJSON data you can find an example from here tools.ietf.org/html/rfc7946#appendix-A.6.
– user30184
Apr 6 at 18:54
If you want to manipulate the original GeoJSON data you can find an example from here tools.ietf.org/html/rfc7946#appendix-A.6.
– user30184
Apr 6 at 18:54
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "79"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fgis.stackexchange.com%2fquestions%2f317976%2fdata-type-for-mixed-representation%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
draft saved
draft discarded
Thanks for contributing an answer to Geographic Information Systems Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fgis.stackexchange.com%2fquestions%2f317976%2fdata-type-for-mixed-representation%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Create geometry column in PostGIS as multipolygon and cast polygons of the source data into multipolygons before inserting them. For example ogr2ogr gdal.org/ogr2ogr.html can do that automatically with
-nlt PROMOTE_TO:MULTI. Another option is to create the geometry column in PostGIS as general "geometry" when it will accept any kind of geometries.– user30184
Apr 6 at 17:58
should the arrays be cast with parenthesis or will square brackets do?
– Jerome
Apr 6 at 18:29
If you want to manipulate the original GeoJSON data you can find an example from here tools.ietf.org/html/rfc7946#appendix-A.6.
– user30184
Apr 6 at 18:54