I have a file containing spatial data that I would like to load into Tableau using AWS. I found documentation that Amazon Redshift supports GeoJSON data through the GEOMETRY data type. I managed to upload the data into an S3 bucket and to create a table in my Redshift cluster through the following query:
CREATE TABLE public.data (
PC6 VARCHAR(100),
Aantal_adr INTEGER,
geometry GEOMETRY,
Gemeente2019 INTEGER,
Wijk2019 INTEGER,
Buurt2019 INTEGER
);
However, when I want to load the GeoJSON file from S3 into the table, the table remains empty and I don't even get an error. How do I get it to work?
I used the following command:
COPY public.data
FROM 's3://path/folder/data.json'
CREDENTIALS 'aws_access_key_id=x;aws_secret_access_key=y'
json 'auto'
region 'eu-west-1';
And this is a sample of my data:
{
"type": "FeatureCollection",
"crs": { "type": "name", "properties": { "name": "urn:ogc:def:crs:OGC:1.3:CRS84" } },
"features": [
{ "type": "Feature", "properties": { "PC6": "1011AB", "Aantal_adr": 32, "Gemeente2019": 363, "Wijk2019": 36304, "Buurt2019": 3630400 }, "geometry": { "type": "Polygon", "coordinates": [ [ [ 4.905027952000069, 52.378425486000026 ], [ 4.905076113000064, 52.37841046300008 ], [ 4.905191411000033, 52.378376782000032 ], [ 4.905214767000075, 52.378369010000029 ], [ 4.905234707000034, 52.378360745000066 ], [ 4.905242522000037, 52.37835616600006 ], [ 4.905248247000031, 52.37835098000005 ], [ 4.905251343000032, 52.378345312000079 ], [ 4.905252578000045, 52.378339070000038 ], [ 4.905252531000031, 52.378332431000047 ], [ 4.905249474000072, 52.378312360000052 ], [ 4.905248898000025, 52.378300391000039 ], [ 4.905250074000037, 52.378294856000082 ], [ 4.905252799000039, 52.378289789000064 ], [ 4.905257110000036, 52.37828554400005 ], [ 4.905262918000062, 52.378281792000053 ], [ 4.905272242000024, 52.378277568000044 ], [ 4.905295016000025, 52.378270656000041 ], [ 4.905320961000029, 52.378264933000082 ], [ 4.90534841300007, 52.378259781000054 ], [ 4.905615848000025, 52.378213457000072 ], [ 4.905672094000067, 52.378204254000082 ], [ 4.905713623000054, 52.378198448000035 ], [ 4.905755373000034, 52.378193294000027 ], [ 4.905797334000056, 52.378188918000035 ], [ 4.905854017000024, 52.378184793000059 ], [ 4.906010385000059, 52.378175414000054 ], [ 4.906049546000077, 52.378171835000046 ], [ 4.906099170000061, 52.378165687000035 ], [ 4.906138697000074, 52.378159097000037 ], [ 4.906179196000039, 52.378151124000055 ], [ 4.906393302000026, 52.378105836000032 ], [ 4.906379850000064, 52.378077200000064 ], [ 4.906376915000067, 52.37806777000003 ], [ 4.906375354000033, 52.37805852300005 ], [ 4.906375845000071, 52.37805008600003 ], [ 4.906379084000037, 52.378043095000066 ], [ 4.906385125000043, 52.378038245000027 ], [ 4.906393308000077, 52.378034955000032 ], [ 4.906413366000038, 52.378029843000036 ], [ 4.906448001000058, 52.378023854000048 ], [ 4.906689757000038, 52.377990125000053 ], [ 4.906724868000026, 52.37798340900008 ], [ 4.906734994000033, 52.377980573000059 ], [ 4.90674369200002, 52.377977198000053 ], [ 4.906750343000056, 52.377973046000079 ], [ 4.906754179000075, 52.377967886000079 ], [ 4.90675559500005, 52.37796090900008 ], [ 4.906755038000028, 52.377952553000057 ], [ 4.906749900000023, 52.377933764000034 ], [ 4.906733622000047, 52.377896754000062 ], [ 4.906462646000023, 52.377214200000026 ], [ 4.906126387000029, 52.377292057000034 ], [ 4.905688946000055, 52.377410774000055 ], [ 4.905016945000057, 52.377587847000029 ], [ 4.90462009700002, 52.377724772000079 ], [ 4.904597594000052, 52.377732947000027 ], [ 4.904546418000052, 52.377751543000045 ], [ 4.904483645000028, 52.377774349000049 ], [ 4.904388632000064, 52.377808869000035 ], [ 4.904220272000032, 52.377870038000026 ], [ 4.904280897000035, 52.377932213000065 ], [ 4.904695869000022, 52.378344313000071 ], [ 4.904749813000024, 52.378331915000047 ], [ 4.904708505000031, 52.378356860000054 ], [ 4.904782821000026, 52.378430663000074 ], [ 4.904842127000052, 52.378489556000034 ], [ 4.905027952000069, 52.378425486000026 ] ] ] } } ] }
