Not able to write multipolygon data into DB

Question

Not able to write multipolygon data into DB

siva-wal opened this issue 5 years ago · 9 comments

Polygon WKT format

MULTIPOLYGON(((40.074964 -3.274777,40.074965 -3.274773,40.074965 -3.274758,40.074969 -3.274738,40.074972 -3.27472,40.074969 -3.274703,40.07497 -3.274686,40.074972 -3.274668,40.074977 -3.274648,40.074984 -3.27463,40.074987 -3.274613,40.074994 -3.274596,40.074997 -3.274585,40.074975 -3.274585,40.074955 -3.274581,40.074949 -3.274574,40.07495 -3.274563,40.074954 -3.274543,40.074954 -3.274524,40.07495 -3.27451,40.074937 -3.274496,40.074927 -3.274468,40.074924 -3.274441,40.074919 -3.274425,40.074919 -3.2744,40.074919 -3.274381,40.074965 -3.274355,40.075004 -3.274338,40.075019 -3.274335,40.075042 -3.274331,40.07506 -3.274329,40.075072 -3.274328,40.075079 -3.274335,40.075092 -3.274336,40.075102 -3.274338,40.075117 -3.274343,40.07515 -3.27434,40.075179 -3.274335,40.075202 -3.274335,40.075219 -3.274335,40.075242 -3.274333,40.075276 -3.274331,40.075302 -3.274333,40.075321 -3.274341,40.075339 -3.274351,40.075354 -3.274356,40.075367 -3.274356,40.07538 -3.274356,40.07539 -3.274358,40.075409 -3.274356,40.075419 -3.274351,40.075425 -3.27437,40.075429 -3.274393,40.075432 -3.274408,40.075437 -3.274423,40.075439 -3.274433,40.075429 -3.27444,40.075405 -3.274445,40.075389 -3.274448,40.075372 -3.27445,40.075357 -3.27445,40.075347 -3.27445,40.075327 -3.274453,40.075317 -3.274453,40.075302 -3.274455,40.07529 -3.274453,40.075277 -3.274451,40.075262 -3.27445,40.075244 -3.274456,40.075237 -3.274468,40.075235 -3.274481,40.075235 -3.274498,40.075219 -3.274503,40.075199 -3.274503,40.07518 -3.274503,40.07518 -3.274514,40.075184 -3.274535,40.075187 -3.27455,40.075187 -3.274566,40.07519 -3.274578,40.075182 -3.274591,40.075162 -3.274596,40.075145 -3.274598,40.075144 -3.274611,40.075152 -3.27463,40.075155 -3.274651,40.075149 -3.274671,40.075164 -3.274676,40.075177 -3.274683,40.075182 -3.274695,40.075184 -3.27471,40.075174 -3.274721,40.075152 -3.274728,40.075142 -3.274733,40.075142 -3.274748,40.075149 -3.274755,40.075162 -3.274756,40.075175 -3.274761,40.075184 -3.274771,40.075184 -3.274788,40.075202 -3.274795,40.07521 -3.274806,40.075219 -3.274816,40.075209 -3.274823,40.075184 -3.274825,40.075164 -3.274826,40.075135 -3.27483,40.075122 -3.274834,40.075085 -3.274811,40.075092 -3.274798,40.075087 -3.27478,40.075064 -3.274788,40.075045 -3.274789,40.07503 -3.274795,40.075014 -3.274789,40.075002 -3.274786,40.074985 -3.274781,40.074974 -3.274776,40.074964 -3.274777,40.074964 -3.274777,40.074962 -3.274786,40.07495 -3.274783,40.074961 -3.274778,40.074964 -3.274777)))'

When I use pure SQL query then I am able to save above format in DB. But I unable save data using active record.

Could you please help

Answer 1 · 2019-10-21T08:07:58.000Z

Could you post the code you are using? It's impossible to tell without it.

…

On Mon, Oct 21, 2019 at 1:47 AM siva-wal ***@***.***> wrote: Polygon WKT format MULTIPOLYGON(((40.074964 -3.274777,40.074965 -3.274773,40.074965 -3.274758,40.074969 -3.274738,40.074972 -3.27472,40.074969 -3.274703,40.07497 -3.274686,40.074972 -3.274668,40.074977 -3.274648,40.074984 -3.27463,40.074987 -3.274613,40.074994 -3.274596,40.074997 -3.274585,40.074975 -3.274585,40.074955 -3.274581,40.074949 -3.274574,40.07495 -3.274563,40.074954 -3.274543,40.074954 -3.274524,40.07495 -3.27451,40.074937 -3.274496,40.074927 -3.274468,40.074924 -3.274441,40.074919 -3.274425,40.074919 -3.2744,40.074919 -3.274381,40.074965 -3.274355,40.075004 -3.274338,40.075019 -3.274335,40.075042 -3.274331,40.07506 -3.274329,40.075072 -3.274328,40.075079 -3.274335,40.075092 -3.274336,40.075102 -3.274338,40.075117 -3.274343,40.07515 -3.27434,40.075179 -3.274335,40.075202 -3.274335,40.075219 -3.274335,40.075242 -3.274333,40.075276 -3.274331,40.075302 -3.274333,40.075321 -3.274341,40.075339 -3.274351,40.075354 -3.274356,40.075367 -3.274356,40.07538 -3.274356,40.07539 -3.274358,40.075409 -3.274356,40.075419 -3.274351,40.075425 -3.27437,40.075429 -3.274393,40.075432 -3.274408,40.075437 -3.274423,40.075439 -3.274433,40.075429 -3.27444,40.075405 -3.274445,40.075389 -3.274448,40.075372 -3.27445,40.075357 -3.27445,40.075347 -3.27445,40.075327 -3.274453,40.075317 -3.274453,40.075302 -3.274455,40.07529 -3.274453,40.075277 -3.274451,40.075262 -3.27445,40.075244 -3.274456,40.075237 -3.274468,40.075235 -3.274481,40.075235 -3.274498,40.075219 -3.274503,40.075199 -3.274503,40.07518 -3.274503,40.07518 -3.274514,40.075184 -3.274535,40.075187 -3.27455,40.075187 -3.274566,40.07519 -3.274578,40.075182 -3.274591,40.075162 -3.274596,40.075145 -3.274598,40.075144 -3.274611,40.075152 -3.27463,40.075155 -3.274651,40.075149 -3.274671,40.075164 -3.274676,40.075177 -3.274683,40.075182 -3.274695,40.075184 -3.27471,40.075174 -3.274721,40.075152 -3.274728,40.075142 -3.274733,40.075142 -3.274748,40.075149 -3.274755,40.075162 -3.274756,40.075175 -3.274761,40.075184 -3.274771,40.075184 -3.274788,40.075202 -3.274795,40.07521 -3.274806,40.075219 -3.274816,40.075209 -3.274823,40.075184 -3.274825,40.075164 -3.274826,40.075135 -3.27483,40.075122 -3.274834,40.075085 -3.274811,40.075092 -3.274798,40.075087 -3.27478,40.075064 -3.274788,40.075045 -3.274789,40.07503 -3.274795,40.075014 -3.274789,40.075002 -3.274786,40.074985 -3.274781,40.074974 -3.274776,40.074964 -3.274777,40.074964 -3.274777,40.074962 -3.274786,40.07495 -3.274783,40.074961 -3.274778,40.074964 -3.274777)))' When I use pure SQL query then I am able to save above format in DB. But I unable save data using active record. Could you please help — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub <#305?email_source=notifications&email_token=AAAFQSHGA3GVHSXNYRE3GRDQPVGBJA5CNFSM4JCY4YS2YY3PNVWWK3TUL52HS4DFUVEXG43VMWVGG33NNVSW45C7NFSM4HTCME3Q>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAAFQSEJQTJ5W4N7L7X45CDQPVGBJANCNFSM4JCY4YSQ> .

Answer 2 · 2019-10-21T13:07:22.000Z

# db/schema.rb
create_table "polygons", force: :cascade do |t|
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
    t.geometry "geopolygon", limit: {:srid=>4326, :type=>"multi_polygon"}
  end

polygon = Polygon.new
polygon.geopolygon = 'MULTIPOLYGON(((40.074964 -3.274777,40.074965 -3.274773,40.074965 -3.274758,40.074969 -3.274738,40.074972 -3.27472,40.074969 -3.274703,40.07497 -3.274686,40.074972 -3.274668,40.074977 -3.274648,40.074984 -3.27463,40.074987 -3.274613,40.074994 -3.274596,40.074997 -3.274585,40.074975 -3.274585,40.074955 -3.274581,40.074949 -3.274574,40.07495 -3.274563,40.074954 -3.274543,40.074954 -3.274524,40.07495 -3.27451,40.074937 -3.274496,40.074927 -3.274468,40.074924 -3.274441,40.074919 -3.274425,40.074919 -3.2744,40.074919 -3.274381,40.074965 -3.274355,40.075004 -3.274338,40.075019 -3.274335,40.075042 -3.274331,40.07506 -3.274329,40.075072 -3.274328,40.075079 -3.274335,40.075092 -3.274336,40.075102 -3.274338,40.075117 -3.274343,40.07515 -3.27434,40.075179 -3.274335,40.075202 -3.274335,40.075219 -3.274335,40.075242 -3.274333,40.075276 -3.274331,40.075302 -3.274333,40.075321 -3.274341,40.075339 -3.274351,40.075354 -3.274356,40.075367 -3.274356,40.07538 -3.274356,40.07539 -3.274358,40.075409 -3.274356,40.075419 -3.274351,40.075425 -3.27437,40.075429 -3.274393,40.075432 -3.274408,40.075437 -3.274423,40.075439 -3.274433,40.075429 -3.27444,40.075405 -3.274445,40.075389 -3.274448,40.075372 -3.27445,40.075357 -3.27445,40.075347 -3.27445,40.075327 -3.274453,40.075317 -3.274453,40.075302 -3.274455,40.07529 -3.274453,40.075277 -3.274451,40.075262 -3.27445,40.075244 -3.274456,40.075237 -3.274468,40.075235 -3.274481,40.075235 -3.274498,40.075219 -3.274503,40.075199 -3.274503,40.07518 -3.274503,40.07518 -3.274514,40.075184 -3.274535,40.075187 -3.27455,40.075187 -3.274566,40.07519 -3.274578,40.075182 -3.274591,40.075162 -3.274596,40.075145 -3.274598,40.075144 -3.274611,40.075152 -3.27463,40.075155 -3.274651,40.075149 -3.274671,40.075164 -3.274676,40.075177 -3.274683,40.075182 -3.274695,40.075184 -3.27471,40.075174 -3.274721,40.075152 -3.274728,40.075142 -3.274733,40.075142 -3.274748,40.075149 -3.274755,40.075162 -3.274756,40.075175 -3.274761,40.075184 -3.274771,40.075184 -3.274788,40.075202 -3.274795,40.07521 -3.274806,40.075219 -3.274816,40.075209 -3.274823,40.075184 -3.274825,40.075164 -3.274826,40.075135 -3.27483,40.075122 -3.274834,40.075085 -3.274811,40.075092 -3.274798,40.075087 -3.27478,40.075064 -3.274788,40.075045 -3.274789,40.07503 -3.274795,40.075014 -3.274789,40.075002 -3.274786,40.074985 -3.274781,40.074974 -3.274776,40.074964 -3.274777,40.074964 -3.274777,40.074962 -3.274786,40.07495 -3.274783,40.074961 -3.274778,40.074964 -3.274777)))'

polygon.save

There is something fishy in retrieving also. I have saved data using SQL(check below screen shot)

But when I retrieving from rails console it returns nil

Answer 3 · 2019-10-24T11:20:29.000Z

@mjy Any update on this issue? or any work around ?

Answer 4 · 2019-10-24T16:44:37.000Z

I just ran this check, showing the geometry is invalid:

SELECT ST_IsValid(ST_GeomFromText('MULTIPOLYGON(((40.074964 -3.274777,40.074965 -3.274773,40.074965 -3.274758,40.074969 -3.274738,40.074972 -3.27472,40.074969 -3.274703,40.07497 -3.274686,40.074972 -3.274668,40.074977 -3.274648,40.074984 -3.27463,40.074987 -3.274613,40.074994 -3.274596,40.074997 -3.274585,40.074975 -3.274585,40.074955 -3.274581,40.074949 -3.274574,40.07495 -3.274563,40.074954 -3.274543,40.074954 -3.274524,40.07495 -3.27451,40.074937 -3.274496,40.074927 -3.274468,40.074924 -3.274441,40.074919 -3.274425,40.074919 -3.2744,40.074919 -3.274381,40.074965 -3.274355,40.075004 -3.274338,40.075019 -3.274335,40.075042 -3.274331,40.07506 -3.274329,40.075072 -3.274328,40.075079 -3.274335,40.075092 -3.274336,40.075102 -3.274338,40.075117 -3.274343,40.07515 -3.27434,40.075179 -3.274335,40.075202 -3.274335,40.075219 -3.274335,40.075242 -3.274333,40.075276 -3.274331,40.075302 -3.274333,40.075321 -3.274341,40.075339 -3.274351,40.075354 -3.274356,40.075367 -3.274356,40.07538 -3.274356,40.07539 -3.274358,40.075409 -3.274356,40.075419 -3.274351,40.075425 -3.27437,40.075429 -3.274393,40.075432 -3.274408,40.075437 -3.274423,40.075439 -3.274433,40.075429 -3.27444,40.075405 -3.274445,40.075389 -3.274448,40.075372 -3.27445,40.075357 -3.27445,40.075347 -3.27445,40.075327 -3.274453,40.075317 -3.274453,40.075302 -3.274455,40.07529 -3.274453,40.075277 -3.274451,40.075262 -3.27445,40.075244 -3.274456,40.075237 -3.274468,40.075235 -3.274481,40.075235 -3.274498,40.075219 -3.274503,40.075199 -3.274503,40.07518 -3.274503,40.07518 -3.274514,40.075184 -3.274535,40.075187 -3.27455,40.075187 -3.274566,40.07519 -3.274578,40.075182 -3.274591,40.075162 -3.274596,40.075145 -3.274598,40.075144 -3.274611,40.075152 -3.27463,40.075155 -3.274651,40.075149 -3.274671,40.075164 -3.274676,40.075177 -3.274683,40.075182 -3.274695,40.075184 -3.27471,40.075174 -3.274721,40.075152 -3.274728,40.075142 -3.274733,40.075142 -3.274748,40.075149 -3.274755,40.075162 -3.274756,40.075175 -3.274761,40.075184 -3.274771,40.075184 -3.274788,40.075202 -3.274795,40.07521 -3.274806,40.075219 -3.274816,40.075209 -3.274823,40.075184 -3.274825,40.075164 -3.274826,40.075135 -3.27483,40.075122 -3.274834,40.075085 -3.274811,40.075092 -3.274798,40.075087 -3.27478,40.075064 -3.274788,40.075045 -3.274789,40.07503 -3.274795,40.075014 -3.274789,40.075002 -3.274786,40.074985 -3.274781,40.074974 -3.274776,40.074964 -3.274777,40.074964 -3.274777,40.074962 -3.274786,40.07495 -3.274783,40.074961 -3.274778,40.074964 -3.274777)))'))
 st_isvalid
------------
 f
(1 row)

and according to ST_IsValidReason:

SELECT ST_IsValidReason(geom)
             st_isvalidreason
---------------------------------------------
 Ring Self-intersection[40.074964 -3.274777]

Remove the self intersection and I bet the geometry will parse. If it's already in Postgis, you could do something like this: UPDATE table SET geom=ST_Buffer(geom, 0) WHERE id=xxx or you could try UPDATE table SET geom=ST_MakeValid(geom) WHERE id = xxx.

Answer 5 · 2019-10-24T16:56:36.000Z

Cross-referencing this very well written comment explaining some of the types of geometry issues you can run into and potential work-arounds: rgeo/rgeo#212 (comment)

Answer 6 · 2019-10-25T11:09:21.000Z

@pedros007
Though the polygon is invalid postgres is allowing me to save using SQL. Please check the attached screenshot. At the same time which I fetch from rails console it is returning nil value

Looks like postgres doing magic. I am using postgres 9.6.3

Answer 7 · 2019-10-29T12:48:43.000Z

@pedros007

After going through POSTGIS doc (https://postgis.net/workshops/postgis-intro/validity.html), I feel even if geometry is not valid postgres is allow us to save. If that is the case then our ORM should return stored value instead of nil. Agree?

Answer 8 · 2019-10-29T15:34:12.000Z

Some tools handle invalid geometries better than others. If you upgrade to rgeo v2.0.0, it may raise an RGeo::Error::InvalidGeometry exception rather than nil geometry (see rgeo/rgeo#183).

In any case, I clean up my geometries via postgis queries such as

UPDATE table SET geom=ST_MakeValid(geom);

to avoid RGeo parsing issues.

Answer 9 · 2019-11-11T05:59:47.000Z

Thanks @pedros007 for your help. I think we can close this issue.