CODEWITHSUNDEEP
jsonamazon-web-servicesamazon-redshiftgeospatialgeojson
sander
sander

Reputation: 141

Load GeoJSON file into redshift using copy command

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 ] ] ] } } ] }

Upvotes: 2

Views: 1112

Answers (1)

Joe Harris
Joe Harris

Reputation: 14035

Loading GeoJSON directly into a GEOMETRY column is not currently supported. However, we will consider adding to our roadmap. When new features are released they are noted in our regular maintenance announcements at the top of the forum.

You can only COPY to GEOMETRY columns from data in text or CSV format. The data must be in the hexadecimal form of the extended well-known binary (EWKB) format … https://docs.aws.amazon.com/redshift/latest/dg/copy-usage_notes-spatial-data.html

Upvotes: 3

Related Questions