How do I plot data stored in an attribute table (really in a database) in qgis

Question

I have a csv of UK postcodes from Ordnance Survey that looks like

SE1 0AA,10,531869,179427,E92000001,E19000003,E18000007,E09000028,E05000536

When I import to QGIS using 'Add Delimited Text Layer', I can declare columns 3 and 4 as x and y and everything plots nicely with a new layer created.

I want to store the same data in PostGIS so I can join other data on the postcode and then plot resulting attributes in QGIS. The postcode table, with the same fields, has no geometry and I can't see a way to declare the x and y columns as coordinates, so it is shown in QGIS as just an attribute table.

Can anyone explain what I have missed? I apologise for any mangled terminology - this may be why I haven't found any solution on the web yet.

Answer 1

Having loaded the data into QGIS using 'Add Delimited Text Layer' I think the easy way would be to save the layer as a shapefile and then import this to PostGIS using Spit or the DB Manager.

Or am I missing something?

Answer 2

If you're putting your delimited text file into a database and then into any GIS, you need an intermediate step. Create a new field (e.g. "Location") of type "geography" (or possibly "geometry"? my database experience is not in PostGIS!), then update it from your X & Y columns. Then when you import it into QGIS, that "Location" field should be automatically recognized as point data, and properly plotted.

I think this is the relevant syntax: PostGIS documentation