Skip to content

Instantly share code, notes, and snippets.

@dlebauer

dlebauer/gist:5863664

Forked from andrewxhill/gist:5495331
Last active December 18, 2015 23:48
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save dlebauer/5863664 to your computer and use it in GitHub Desktop.
Save dlebauer/5863664 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
gistfile1.sql
### this works
SELECT cartodb_id, ST_GeomFromText(replace(replace(replace(replace(replace(geometry, '<Polygon><outerBoundaryIs><LinearRing><coordinates>', 'Linestring('), '</coordinates></LinearRing></outerBoundaryIs></Polygon>', ')'), ',', '$'), ' ', ','), '$', ' '),4326) geom FROM test_yield_county
### the official query
delete FROM test_yield_county where geometry = ''
with f as (SELECT cartodb_id, ST_GeomFromText(replace(replace(replace(replace(replace(geometry, '<Polygon><outerBoundaryIs><LinearRing><coordinates>', 'Linestring('), '</coordinates></LinearRing></outerBoundaryIs></Polygon>', ')'), ',', '$'), ' ', ','), '$', ' '),4326) geom FROM test_yield_county order by geometry desc)
update test_yield_county d
set the_geom = (
SELECT ST_MakePolygon(ST_AddPoint(f.geom, ST_StartPoint(f.geom))) from f WHERE cartodb_id = d.cartodb_id LIMIT 1
)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment