Select to view content in your preferred language

From data table to spatial view

1760
7
05-01-2020 08:48 PM
PamelaLandy
Occasional Contributor

Hi all ,

I have an a table with x, y coordinates in my DB oracle .  that I have an a  GDB on for my spatial side, how can I convert that table to a point layer inside my GDB. I have tried doing a view but it is still a nonspatial datatable when I view it in desktop arcmap
0 Kudos
7 Replies
DavidPike
MVP Frequent Contributor

Hi Pamela, 

If you go to 'Add Data' and point to your table, it will then get added to ArcMap Table of Contents (TOC) on the left-hand-side.

If you then right click on the table, go to 'Display XY' where a popup will then appear for you to specify your coordinate columns and coordinate system (be very aware of the coordinate system you are using, is it lat long or NAD etc?). when you click 'OK' the data should be displayed spatially in your map frame.

At the moment, that data is only being displayed on the fly and does not 'exist' in a spatial context.  The final step is to right-click on the newly added displayed data in the TOC and --> Data --> export Data to save it as a shapefile (.shp) or a feature class within a GDB.

PamelaLandy
Occasional Contributor

Hi Dave , It is a possibility but in postgres I have seen that through a line like this in the view , it is possible to give it the spatial reference

st_point((table.attrlong)::double precision, (table.attrlatt)::double precision, 4326) AS shape

Although I already configured the libst_shapelib.so library I am still looking for the same in oracle.

0 Kudos
DavidPike
MVP Frequent Contributor

I've not implemented it myself but

Spatially Enabling a Table 

Looks promising.

0 Kudos
by Anonymous User
Not applicable

Hi Pamela

You can use sde.st_point or sde.st_geometry for creating the spatial view. Make sure you add anumeric unique identifier (similar to an OBJECTID field).

You can also try "Make query layer" from ArcGIS Desktop, but this do not create the view in the database, so in the mxd...

Regards

Jesús de Diego

PamelaLandy
Occasional Contributor

Hi Jesus , Do you have an example or reference for how to generate a spatial view from a table ( points coord(x y) ) using the sde.st_point or sde.st_geometry in oracle.

Thanks in advanced

0 Kudos
MatthewCarey
Occasional Contributor

Hi Pamela, here's an example based on one that's worked for me:

CREATE OR REPLACE FORCE EDITIONABLE VIEW "SCHEMA_NAME"."VIEW_NAME" ("ID", "SHAPE", "JOB_NUMBER") AS
SELECT ID, sde.st_geometry(device_longitude,device_latitude,null,null,4326) SHAPE, JOB_NUMBER


FROM the_source_non_spatial_table;

Database people will have to excuse me if the syntax is poorly done, I'm a bit of an amateur at it. 

I guess you can do a similar thing in an Insert or Update statement to make it a permanent point feature class. When you do that you're supposed to create the ObjectID the right way, there's a reference here:

How To: Populate an ArcSDE maintained row ID column when inserting a new ST_Geometry feature with SQ... 

Hope this helps!

Matt

CraigSwadner
Occasional Contributor

Select * into tablename from view

Craig Swadner (GIS Coordinator)

City of Cape Coral

1015 Cultural Park Blvd.

Cape Coral, Fl 33990

Did you know the best way to report issues to ITS is to use the Service Desk system? This will ensure someone in ITS gets your request and allows us to track the progress. Please enter all your issues and/or questions by clicking https://breeze.cape.capecoral.net

0 Kudos