I am trying to bring in data into arcmap from my PostgreSql + Postgis database. The data are points (using ST_Geometry POINTZ types) containing various properties/attibutes. So I successfully connected to my database by creating a new query layer, but many common sql query I put in the 'query' section gives me errors. Below is a screenshot of the query layer dialog.
Here are some errors I get when performing the sql queries
select * from firstdb.public.purcell7 -> attribute conversion error
select geom from firstdb.public.purcell7 -> at least one fixed-point or string data type column must be present to generate unique identifer
But if I do
select id from firstdb.public.purcell7 the validate button does not produce any errors
and in arcmap in the table of contents I get
(this only contains data from the id column of my table- obviously)
Then if I right-click the firstdb I can 'Add Table' and select the entire table I want (which is what the sql command : select * from firstdb.public.purcell7 should have done but didn't successfully do). Then I select the 'Display XY Data' option for that table where the x and y fields are automatically filled in my x, y columns of the data and hit 'ok' and I get the following warning:
Now the table I am trying to import has OIDs setup, there is a spatial index associated with the table, and there is a proper ST_Geometry associated with the data (POINTZ type).
How do I properly bring in my spatial data from my postgresql + postgis database so that I can view, query, select the data in arcmap?
Solved! Go to Solution.
Removing the 'normal' column resolved the issue. The column 'normal' was set as an array of doubles (a three component vector). I guess ERSI does not recognized this data type. The data contained in the normal column was also in the table in 3 separate columns (nx, ny, nz) so deleting the normal column was not an issue.