d3zcbGDj

1ALTER TABLE crowscores
2  DROP COLUMN IF EXISTS id;
3ALTER TABLE crowscores 
4  ADD COLUMN id SERIAL PRIMARY KEY;
5ALTER TABLE crowscores
6  DROP COLUMN IF EXISTS geom;
7ALTER TABLE crowscores
8  ADD COLUMN geom geometry;
9UPDATE crowscores
10	SET geom = 
11	  CASE 
12	    WHEN 
13	      "X" is not null
14	    THEN 
15	      ST_PointFromText('POINT('||"X"::double precision||' '||"Y"::double precision||')',28992)
16            WHEN 
17              maxx is not null
18             -- create polygon from minmax
19           THEN ST_SetSRID(ST_ENVELOPE(('LINESTRING('||minx||' '||miny||', '||maxx||' '||maxy||')')::geometry), 28992) 
20	    WHEN 
21	      lon is not null
22	    THEN 
23	      ST_TRANSFORM(ST_PointFromText('POINT('||"lon"::double precision||' '||"lat"::double precision||')',4326),28992)
24	    WHEN 
25	      "RD-X" is not null and lat is null
26	    THEN 
27	      ST_PointFromText('POINT('||"RD-X"::double precision||' '||"RD-Y"::double precision||')',28992)
28           ELSE null END;
29
30CREATE INDEX geom_crowscores ON crowscores USING GIST(geom);
31
32UPDATE crowscores
33       SET (lat,lon) = (ST_Y(ST_TRANSFORM(ST_CENTROID(geom), 4326)), ST_X(ST_TRANSFORM(ST_CENTROID(geom), 4326)))
34      WHERE lat is null or lat > 100