1

I want to store WKT that can be quite large but I'm running into the 32K limit while storing them in object values.

create table A (id integer, wkt object);
nicerobot
  • 9,145
  • 6
  • 42
  • 44
  • How are you storing the data in an object? is this a property like wkt['str']? Otherwise I would recommend you store it as a string or geo_shape (depending on your requirements) – claus Dec 22 '16 at 15:49
  • @claus we're using an key-value because we don't know ahead of time what the wkt will represent and there can be multiple per types of wkt geometries per related-shape. For example, a polygon might have wkt for various buffers, alpha shapes, simplification tolerances, ... flatten that object into a table is doable but this was just nice because it retains all the data as a single row. My understanding of geo_shape is that we can not directly get the wkt back out and we need the wkt for doing spatial queries (see my other question http://stackoverflow.com/questions/41286539) – nicerobot Dec 22 '16 at 16:28
  • Yes, you can't get the WKT back when using geo_shape ... and now it depends if you know the keys before you insert them, since you can predefine INDEX OFF (or INDEX USING PLAIN, ... ) for each expected column, which would then let you insert the WKT string :) – claus Dec 22 '16 at 17:50
  • @claus How do i query the WKT out of a geo_shape? When I query it, i get GeoJSON. – nicerobot Dec 22 '16 at 18:28
  • 1
    hmm you can't, I have added that as a feature idea to our backlog though ;) – claus Dec 22 '16 at 21:56
  • Thanks. We've currently worked around it by simplifying polygons with increasing tolerances until they're smaller than 32K. This isn't a great concern and only arose out of problem experienced with spatial joins http://stackoverflow.com/questions/41286539 – nicerobot Dec 22 '16 at 22:00

1 Answers1

0

So there is a way to store longer strings in objects:

CREATE TABLE IF NOT EXISTS A (
    "id" INTEGER,
    "wkt" OBJECT (IGNORED)
)

By using ignored the entire object is not indexed, which also prohibits it from being used in other SQL parts properly (they will always do a full table scan).

However, subscripts work just fine.

For other readers: WKT can also be stored as geo_shape type as well, or used with match directly.

claus
  • 377
  • 2
  • 9