Database returns nil for valid MultiPolygon

[markborkum]

PostGIS version is 2.5.0 installed via Homebrew.

Rubygem versions (from Gemfile.lock):

• activerecord-postgis-adapter (5.2.1)
• activerecord (~> 5.1)
• rgeo-activerecord (~> 6.0)

I am trying to import the contents of a CSV file. The geometries are saved "successfully". However, for certain rows of the CSV file, attempts to retrieve the data from the corresponding records in the database using activerecord-postgis-adapter for deserialization return nil.

For example, line 156407 of the CSV file (c.f., https://dev.socrata.com/foundry/data.sfgov.org/2s2t-jwzp) contains the following well-known text for the geometry for AT&T Park in San Francisco:

MULTIPOLYGON (((-122.38881976231 37.77923523529, -122.388804119691 37.779248288341, -122.388804089115 37.779249576693, -122.388736336901 37.779305686423, -122.388735345943 37.779305673294, -122.388703640352 37.779332128723, -122.387810661362 37.778614548526, -122.387814609477 37.778585892065, -122.387811140111 37.778585064958, -122.387805638074 37.778578430108, -122.387805614265 37.778578395859, -122.387816682916 37.77854710155, -122.387824621151 37.778543969508, -122.387829917765 37.778545142646, -122.387843084163 37.778523260839, -122.387896335497 37.778468172867, -122.387962000419 37.77843477294, -122.388048278897 37.778413723325, -122.388043887139 37.778360677165, -122.387972853509 37.778366564486, -122.387972011798 37.778359120335, -122.387968898485 37.778331585446, -122.387967645497 37.778320504815, -122.387967634233 37.778320400298, -122.387963916965 37.778287525117, -122.387963895553 37.778287339509, -122.387960863375 37.778260520923, -122.387954289726 37.778202371488, -122.387949281041 37.778166385522, -122.389835233078 37.77745188375, -122.38986971218 37.777471016061, -122.390045769351 37.777404202489, -122.390062963308 37.77741191262, -122.390063248196 37.777416158076, -122.390111512516 37.777443103221, -122.39010759583 37.777448050188, -122.390152249683 37.777471863556, -122.390158544926 37.777464862711, -122.390168691674 37.777463362365, -122.390201430179 37.777481801759, -122.390201450581 37.777481838709, -122.390202683332 37.77748884698, -122.390196947837 37.777495673325, -122.390247420396 37.777522166354, -122.39023235128 37.777538456127, -122.390224289065 37.77754780037, -122.39035498364 37.777624142545, -122.390567391687 37.777748214387, -122.390566999735 37.77774863854, -122.390567525042 37.777748947834, -122.390591503993 37.7777222051, -122.390775600375 37.777511131413, -122.391163247776 37.777816527598, -122.391163268176 37.777816568151, -122.390973596727 37.778020188724, -122.391006786462 37.778038443423, -122.391006820466 37.778038506507, -122.390993361707 37.778053904334, -122.390976857436 37.778045038757, -122.390835686813 37.778206376238, -122.390850458566 37.778214261624, -122.390850493704 37.778214324709, -122.390837583723 37.77823041653, -122.390813486805 37.77821709639, -122.390780069996 37.778257200123, -122.390785063234 37.77830858422, -122.390823399516 37.778336990838, -122.39085264379 37.778313813984, -122.390867077617 37.778314179809, -122.390956144591 37.77838670738, -122.390956191064 37.778386792093, -122.390956100561 37.778396396251, -122.390879915489 37.77845704472, -122.390881401454 37.77845834825, -122.390881435458 37.778458410433, -122.390487907156 37.778770264228, -122.389936204412 37.779207393523, -122.389933974451 37.779205292256, -122.389447210953 37.77959773692, -122.389404939621 37.779565835728, -122.389356546938 37.779604319533, -122.389385249504 37.779627102385, -122.38938530279 37.779627181697, -122.389385283306 37.779637140865, -122.389293394157 37.779709285436, -122.389280047221 37.779709292973, -122.389252372555 37.779687162918, -122.389203439037 37.779726076137, -122.389151650465 37.779682070992, -122.388793058965 37.779398370319, -122.388793015881 37.779398309032, -122.388792658821 37.779394941069, -122.38877692709 37.779382491581, -122.388776873802 37.779382415873, -122.388783272226 37.779377285544, -122.388759566159 37.779358014369, -122.388869280695 37.779274401268, -122.38881976231 37.77923523529), (-122.389625551223 37.778787753654, -122.390022584172 37.778474208924, -122.390046898675 37.7784935198, -122.390059946264 37.778481594162, -122.390088152711 37.778453568019, -122.390103437588 37.778424777596, -122.390111347401 37.778394833801, -122.390116110847 37.778363260377, -122.390114143257 37.778330031176, -122.390101669875 37.778301407733, -122.39008430577 37.77827219618, -122.390061702629 37.778247821001, -122.390031474075 37.778223121195, -122.389989871514 37.778201471636, -122.38994266161 37.778176904607, -122.389925657026 37.778195876403, -122.389420752733 37.777912640753, -122.389420055657 37.777912787251, -122.389250616934 37.777901612833, -122.389172610862 37.777849327882, -122.389141368803 37.777790396354, -122.389111008608 37.777802237266, -122.388743193 37.777931686253, -122.388722412831 37.777990487231, -122.388469735593 37.778085792145, -122.38846982455 37.778086657111, -122.388472019367 37.778085876226, -122.388497455754 37.778343820929, -122.388498808883 37.778343734243, -122.388521469209 37.778565731995, -122.388520293163 37.778565807963, -122.388530573085 37.778646332764, -122.388532309016 37.778645930041, -122.389048331131 37.779055009184, -122.38888666188 37.779184683975, -122.388944286357 37.779233343004, -122.389125460634 37.779074434049, -122.389125443332 37.779074769197, -122.389125470593 37.779074746687, -122.389164929554 37.779086193004, -122.389274409874 37.778979794197, -122.389366956551 37.778929832434, -122.389625551223 37.778787753654), (-122.388372451429 37.778574605156, -122.388355623774 37.77841410796, -122.388347867669 37.778355167791, -122.388250520718 37.77836134423, -122.388254674487 37.778407281707, -122.388234227605 37.778408689096, -122.388248431304 37.77854105345, -122.388264910509 37.778540216988, -122.388269666512 37.778581336459, -122.388328283828 37.778577498445, -122.388372451429 37.778574605156), (-122.388423918722 37.778350342391, -122.388445393576 37.778569826769, -122.388459002116 37.778568935623, -122.388518549129 37.77856503223, -122.388496168064 37.778345758216, -122.388423918722 37.778350342391)))

The RGeo::Geos.factory.parse_wkt method returns an instance of the RGeo::Geos::CAPIMultiPolygonImpl class. The instance is serialized and written to the database successfully. However, attempts to retrieve the instance from the database return nil. (This is surprising because there is a NOT NULL constraint on the database column for the geometry.)

Manually executed SQL statements that call the ST_IsValid function on the saved geometry for the record return true.

Manually executed SQL statements that call the ST_AsText function on the saved geometry for the record return the original well-known text for the geometry. This suggests that there may be an issue with deserialization.

The migration for the database column is:

t.geometry :the_geom, geographic: true, null: false

[markborkum]

In the ActiveRecord::ConnectionAdapters::PostGIS::OID::Spatial#parser_wkt private instance method (spatial.rb#L92), the call to the wkt_parser private instance method returns an instance of the RGeo::WKRep::WKBParser class, but the return value for the subsequent call to the parse instance method is nil.

[teeparham]

WKBParser is a binary string parser. If the polygon string is plain text, it should be returning a WKTParser (a text parser). Look into why #binary_string? is true.

If it still doesn't parse after resolving that issue, it is likely an issue with the rgeo gem.

Here's a similar test that might also help you isolate the problem, and is an example of where you could add a failing test in a PR (if needed):
https://github.com/rgeo/rgeo/blob/master/test/wkrep/wkt_parser_test.rb#L331

[markborkum]

The string argument is:

0106000020E610000001000000010300000004000000620000003DCA486CE2985EC010BBEDFABDE34240DEA7AC2AE2985EC0BDF16C68BEE3424033D38B2AE2985EC0BDA83B73BEE342406D705F0EE1985EC00663EA49C0E34240F767370AE1985EC04731CE49C0E3424079C93B85E0985EC02ECBBA27C1E34240C1E5CEE3D1985EC0F30D3BA4A9E3424058275EF4D1985EC0E9C5D7B3A8E3424047F3D0E5D1985EC00793E7ACA8E34240DD2EBDCED1985EC0AA573F75A8E342404C9EA3CED1985EC014CBF574A8E34240EA7D10FDD1985EC0C3C6716EA7E34240561B5C1ED2985EC077C42B54A7E34240824D9334D2985EC0E70F035EA7E342406E9DCC6BD2985EC0493D74A6A6E34240AACC264BD3985EC067B857D8A4E34240FBF8915ED4985EC05AEC29C0A3E342406EC872C8D5985EC02038960FA3E342402F2B07B6D5985EC042759A52A1E34240A563178CD4985EC03562FD83A1E34240E39B8F88D4985EC0EA308B45A1E34240EDB6807BD4985EC09A77905EA0E3424035543F76D4985EC04AFE9C01A0E34240FB3B3376D4985EC0768BBC00A0E3424023D99B66D4985EC093A1F5EC9EE3424073DB8466D4985EC07F0A67EB9EE34240B514CD59D4985EC0EC906E0A9EE34240D4AD3A3ED4985EC0F59AA3229CE34240FBA43829D4985EC0EE54C4F49AE3424082397A0FF3985EC0BD75178B83E34240E6E017A0F3985EC0F2C8952B84E3424091D38782F6985EC04BBB1CFB81E342407AB2A5CAF6985EC0A31CCA3B82E34240E597D7CBF6985EC0C128675F82E3424028034796F7985EC0E06A6F4183E34240B580D985F7985EC0BEF2EE6A83E342405B362441F8985EC040C4B13284E3424093AD8B5BF8985EC00291F7F783E342406EAA1A86F8985EC0129961EB83E34240295E6B0FF9985EC021E50F8684E342403846810FF9985EC09B3E5F8684E342403CEEAC14F9985EC0556429C184E342405F7D9EFCF8985EC02FDB6CFA84E34240BFFC50D0F9985EC0E733AAD885E34240AFA51C91F9985EC05139506186E34240E6E84B6FF9985EC03CD5B2AF86E342401F247893FB985EC0C9671A3089E34240808B5F0EFF985EC044A8E4408DE342408EB0BA0CFF985EC0D78473448DE34240D8BBEE0EFF985EC004B90B478DE34240B4EF8173FF985EC08523B6668CE34240DDEBA97702995EC0EFD8187C85E34240162693D108995EC02D29F27D8FE34240980DA9D108995EC06D3F497E8FE34240AFE21EB605995EC04219612A96E342409317544106995EC02AC482C396E34240869A784106995EC0F63C0AC496E34240055F050906995EC0E0D1344597E342408D0BCCC305995EC06023D6FA96E342402E3EAF7303995EC08EBD3B449CE34240BA4AA4B103995EC05A7A61869CE342406205CAB103995EC0B2F3E8869CE34240D408A47B03995EC0D9DFE50D9DE34240722A921603995EC07117299E9CE34240EE23698A02995EC0D13393EE9DE34240BE965A9F02995EC0E2B59D9D9FE34240B2DB254003995EC04875E88BA0E3424086A8CEBA03995EC00C8B7CC99FE34240AEDC58F703995EC078258ECC9FE3424012CCEB6C05995EC04BEBF52CA2E3424079B21D6D05995EC0C2D6AB2DA2E342403285BC6C05995EC0749C3C7EA2E34240106C312D04995EC0E434FE7AA4E3424003F76C3304995EC01684ED85A4E34240F679913304995EC08D0D7386A4E34240FCADFDC0FD985EC070FA77BEAEE34240755EFBB6F4985EC0C9FD5F11BDE3424072F7A0ADF4985EC0098EBFFFBCE34240F0A3FEB3EC985EC0B30DD0DBC9E34240AD24B202EC985EC0EDC334D0C8E3424043E5B837EB985EC0771B0813CAE34240870A1CB0EB985EC0CFE825D2CAE34240AC4155B0EB985EC00B3BD0D2CAE34240F35540B0EB985EC08D615B26CBE34240001DD72EEA985EC0E6AA8C83CDE342402CF3DBF6E9985EC069DA9C83CDE34240E880C882E9985EC003ECF8C9CCE34240E6898AB5E8985EC0656C6610CEE342409FFB52DCE7985EC01718429FCCE34240A84A48FCE1985EC0B4896753C3E34240D1071AFCE1985EC0D1ECE352C3E34240E9A39AFAE1985EC09447A336C3E342409FD29EB8E1985EC02C3534CEC2E34240ED9A65B8E1985EC042A091CDC2E3424052DC3BD3E1985EC0105488A2C2E342403EAACD6FE1985EC091CBDF00C2E3424043C0FA3BE3985EC02C077A43BFE342403DCA486CE2985EC010BBEDFABDE342402B000000150C02A0EF985EC0103C2E51AFE3424017EE4821F6985EC0D50DFA0AA5E34240C86D4487F6985EC02FD8F7ACA5E34240BD2BFEBDF6985EC05CBBED48A5E34240DA9C4C34F7985EC04E0CD45DA4E34240D99F6874F7985EC0E015516CA3E34240B1B89595F7985EC07C462171A2E34240C26E90A9F7985EC013DD4568A1E34240BCBF4FA1F7985EC0A2B28651A0E34240998EFE6CF7985EC06E526A619FE34240C3FD2924F7985EC056FD5E6C9EE34240AC0D5CC5F6985EC0F8B0E59F9DE3424005649246F6985EC00443B3D09CE3424021FB1398F5985EC01730171B9CE3424087BB10D2F4985EC011E5014D9BE342401233BE8AF4985EC0378427EC9BE34240CE570545EC985EC0EE9633A492E3424003DD1842EC985EC00E316EA592E342407B6B6B7BE9985EC0CB4FB14792E34240AE093D34E8985EC0013C189190E34240E42133B1E7985EC0DDBDBDA28EE34240091FDC31E7985EC014E811068FE34240911E212BE1985EC0487DF74393E342403595F8D3E0985EC0CDA0393195E34240E5762AB0DC985EC05D5FB35098E3424035FB89B0DC985EC073DFF45798E34240E9A5BEB9DC985EC062EF675198E34240BBC26E24DD985EC085F733C5A0E34240FFAB1B2ADD985EC063CF79C4A0E34240FA022789DD985EC0385ABA0AA8E34240EA3D3884DD985EC0117E5D0BA8E342405C3956AFDD985EC0942FDBAEAAE34240702A9EB6DD985EC045587AABAAE3424058B1F82AE6985EC0791D1513B8E3424003A8E184E3985EC0BD9BDF52BCE34240A4779376E4985EC0D5130EEBBDE34240B2DD796EE7985EC0F1B107B6B8E34240C249676EE7985EC0816BD7B8B8E34240348F846EE7985EC08014A7B8B8E34240D04B0514E8985EC0D1DBAB18B9E3424007E536DFE9985EC01429229CB5E34240E5216263EB985EC0661706F9B3E34240150C02A0EF985EC0103C2E51AFE342400B00000084632018DB985EC011522955A8E34240EED48BD1DA985EC07437D012A3E3424005C703B1DA985EC0DF2A6324A1E34240E248B618D9985EC01FF83258A1E34240365C222AD9985EC02DF38CD9A2E3424015B05FD4D8985EC0764B5BE5A2E3424023CBF20FD9985EC0A094B53BA7E3424083341155D9985EC0C74AB134A7E34240AAEC0369D9985EC006AFA08DA8E34240CCC9DF5EDA985EC0849C6E6DA8E3424084632018DB985EC011522955A8E34240060000004AF9FEEFDB985EC02BB3E8FBA0E34240366C114ADC985EC008D0132DA8E34240337B2583DC985EC056179A25A8E342408099E77CDD985EC0579EDB04A8E34240321D081FDD985EC04F4274D5A0E342404AF9FEEFDB985EC02BB3E8FBA0E34240

The binary_string? private instance method returns true for the above data because the last clause succeeds (i.e., the regular expression matches).

Manually calling the WKBParser.parse instance method for the above data raises the following exception:

#<RGeo::Error::ParseError: Unknown type value: 536870918.>

Interestingly, the exception is not raised during the call to the parser_wkt private instance method.

[culov]

Has anyone found a solution for this?

[fxckdead]

Has anyone found a solution for this?

I just found what was wrong in my case, i was trying to parse a ewkb that has a SRID but didn't specified it at the Parser, so i got the RGeo::Error::ParseError: Unknown type value: 536870917., i found the solution at the test suit

the snippet:

factory_generator_ = RGeo::Cartesian.preferred_factory(srid: 4326)
parser = RGeo::WKRep::WKBParser.new(factory_generator_, support_ewkb: true)
parser.parse("0103000020E61000000100000006000000A25D85949FE964C011DF89592F0455C055302AA9130A61405B94D920938053C0BD18CA8976014F4087A757CA320C3DC0840D4FAF94E363406AF6402B303047C02B90740C03472C40884FA07494FCFDBFA25D85949FE964C011DF89592F0455C0")

result:

=> #<RGeo::Geos::CAPIPolygonImpl:0x3fcf87cb5dcc "POLYGON ((-167.30073 -84.06539, 136.3149 -78.00898, 62.01143 -29.04765, 159.1119 -46.37647, 14.1386951343812 -1.87416501576533, -167.30073 -84.06539))">

[mjy]

Seems issue can be closed?

[keithdoggett]

I'm going to leave this open for now because it could be related to #312 and rgeo/rgeo#218.

[keithdoggett]

Closing since this is caused by the issue I talk about in #319. The original polygon was written was a GEOS factory, but read with a spherical factory (due to the migration specifying geographic). The spherical factory cannot properly validate it due to rgeo/rgeo#212 and thus returned nil. Now it would raise RGeo::Error::InvalidGeometry (LinearRing failed ring test).