I want to copy data in a table within postgis to another table, so I use following sql statement:
INSERT INTO yourtablename (SELECT TO_DATE(name, 'YYYY-MM-DD'), TO_NUMBER(area, '9999999999.999999999'), TO_NUMBER(fd_depth, '9999'), TO_DATE(fdate, 'YYYY-MM-DD'), town_name, address, description, effect_range FROM yourtmptablename);
Notice: Before you executing above snippet, please make sure yourtablename has same column names as output of column names from SELECT statement.
I have encounter data format problem about “-"(hyphen) for numeric column as I’m using above snippet with dataset from Taipei opendata platform. Hyphen exists in area column and this column is set to character-vary type. It’s so weird for me because this column represents how much of effect range a flooding had impacted. But hyphen cannot exist in numeric column in a table, I replace hyphen with zero.
UPDATE yourtmptablename SET area = 0 where area = '-';
I also wonder how to read/understand info from a geometry column because its value is liked 0106000020F00E00000300000001030000000100000005000000D1B35049A3635E40FE2CCE1C9D093940BD22F27CA3635E40703EF2DF95093940CCE9F5C39A635E406C16280197093940043DD6B19A635E4086DED6339E093940D1B35049A3635E40FE2CCE1C9D093940010300000001000000070000009B92E84093635E403BE1D788B60939403B1FE65E93635E40E7962760B1093940768B8D288C635E400261CB51B209394076FA459F8A635E404191F89FC609394065DD759F8B635E406804983AC709394069D5C2F78C635E407043DA16B80939409B92E84093635E403BE1D788B609394001030000000100000005000000F5A05D0F93635E4046BB6DC9D70939401C309D3A93635E4022EFCEE4D309394026B3F3938B635E40E7A1F00DD5093940E12EDC3C8B635E408E3C3274D8093940F5A05D0F93635E4046BB6DC9D7093940. It’s hard to realize.
The solution is ST_AsText(column_name) of postgis, please refer following example snippet.
SELECT ST_AsText(effect_range) FROM yourtablename;
The result is MULTIPOLYGON(((121.556841210185 25.0375535968169,121.556853519879 25.0374431578079,121.55632113471 25.0374603960867,121.556316813676 25.0375702285369,121.556841210185 25.0375535968169)),((121.555862643358 25.0379415061045,121.555869793633 25.0378627869276,121.555429590442 25.0378771897822,121.555335825289 25.038187025247,121.555396905043 25.0381962414871,121.555478992717 25.0379652293081,121.555862643358 25.0379415061045)),((121.555850831446 25.0384488957309,121.555861142639 25.0383894925318,121.555394161213 25.0384072029573,121.555373396895 25.0384590742729,121.555850831446 25.0384488957309)))
Reference link:
