saving results of ST_offsetcurve to new table with geometry and fields from original data table

Question

I am trying to create lines parallel to some road centerlines, using distances from the attribute table specific to each line. I'm trying to figure out how to get it to save to a new table with geometry that I can view in qgis or arcgis. I've imported the source data (which was a shapefile) into a postgres database, and ran this query:

select ST_OffsetCurve(
test_data_.geom, test_data_.buffer_dis, 'join=mitre mitre_limit=5.0')
into test_data_left from test_data_;

It writes out to a new table called test_data_left as specified above, but I can't see this table (or the original for that matter) in either qgis or arcgis, when I try to load directly from the database. I tried to export the test_data_left to shapefile, which it does, but when I try to laod it in qgis it appears to be empty, and arcgis tells me the number or shapes don't match the number of records. I think I am missing a step or two. I'd also like to be able to associate some of the fields in the original table with the ouput but am not sure how to do that in this expression?

UPDATE:

I found this useful thread Creating a spatial PostGIS table as a result of a PostGIS query and am working on adding geometry and ID fields, hoping that will get me part of the way there. Still looking for a way to add some of the original fields and values to the output as well.

Answer 1

First, use only the select and st_astext() to check the results: (adapt to your fields)

select test_data_,id_field,test_data_.geom,st_astext(ST_OffsetCurve(
test_data_.geom, test_data_.buffer_dis, 'join=mitre mitre_limit=5.0'))

If all right then:

select test_data_,id_field,test_data_.geom,ST_OffsetCurve(
test_data_.geom, test_data_.buffer_dis, 'join=mitre mitre_limit=5.0')
into test_data_left from test_data_;