GIS_SISSIPPI

Oracle Map Viewer (and Spatial Queries and Javascript using 12c v2 API)

Terms

Oracle SRID (Spatial Ref ID)
SRID is sometimes called an "EPSG" code
European Petroleum Survey Group (EPSG).

Common Ref Systems

GRS80 = Geodetic Reference System 80
WGS84 = World Geodetic System 1984
NAD27 = North American Datum of 1983 (SRID 26916)

Location info using SRID=26916:

 y      4,400,000 4180946.6978492998 to 4625448.000535274
 x        550,000  403443.0718977172 to  692180.1995906243
 height   500,000

 400000 to  670000
4200000 to 4700000

srid conversions

to convert one srid to another use this function which returns the new sdo_geometry
SDO_CS.TRANSFORM(geom IN SDO_GEOMETRY,  to_sridnum IN NUMBER)
SDO_CS.TRANSFORM(geom IN SDO_GEOMETRY,  to_srname IN VARCHAR2)

Forums:

SPATIAL
  https://community.oracle.com/community/database/oracle-database-options/spatial

MAPVIEWER
 https://community.oracle.com/community/fusion_middleware/application_server/mapviewer


Useful queries

MDSYS.SDO_UNITS_OF_MEASURE; --MILE, KM, etc...

--show gtypes in sdo_geometry column
SELECT tbl.geometry.sdo_gtype, count(*) from MILE_MARKERS_IN tbl group by tbl.geometry.sdo_gtype



--get x/y lat/lon of each vertices in polygon!!!
select name,    geo_tbl.x longitude,    geo_tbl.y latitude  from county,
    table (sdo_util.getvertices(geometry)) geo_tbl
      where name like '%Hendric%';


select ROUTE_ALIAS,OBJECTID , geo_tbl.x longitude, geo_tbl.y latitude from COMMUTER_CORRIDORS,
  table(sdo_util.getvertices(shape)) geo_tbl
   where objectid=154
    order by 2 desc;


--get all SRID in a table
select distinct tbl.shape.SDO_SRID  from COMMUTER_CORRIDORS tbl;
  

--show bounding box (min/max x/y of geo table column)
SELECT dim.*
  FROM user_sdo_geom_metadata usgm,
  TABLE(usgm.diminfo) dim
WHERE table_name = 'DISTRICTS';

--list all spatial indexes
select INDEX_NAME from USER_SDO_INDEX_INFO

select * from user_sdo_geom_metadata where table_name='MILE_MARKERS_IN' ;

--show the srid of an sdo_geometry column
select tbl.shape.SDO_SRID from COMMUTER_CORRIDORS tbl;

--srid descriptions
select srid, cs_name from MDSYS.CS_SRS where srid in (3785,8265,40983);

3D to 2D

 SDO_CS.MAKE_2D

The mile marker map I found appeared to have LRS coordinates, so this package correctly converted the 3d to 2d (with correct gtype)

select post_name,
  SDO_CS.MAKE_2D(SDO_LRS.CONVERT_TO_STD_GEOM_3D(tbl.geometry), 26916)
from MILE_MARKERS_IN tbl where post_name like 'S_1%';


select distinct SDO_CS.MAKE_2D(SDO_LRS.CONVERT_TO_STD_GEOM_3D(tbl.geometry), 26916).sdo_gtype from MILE_MARKERS_IN tbl;
--shows correct 2d gtype of :  2001

create table MILE_MARKERS_IN as 
  select tbl.ROUTE_ID, tbl.POST_NAME,  
     substr(POST_NAME,   0, instr(POST_NAME,'_',-1) -1) route_alias,
     substr(POST_NAME,   instr(POST_NAME,'_',-1)+1,  4) mm,  
     SDO_CS.MAKE_2D(SDO_LRS.CONVERT_TO_STD_GEOM_3D(tbl.geometry), 26916) geo
  from MILE_MARKERS_IN_3d tbl order by 1,2;  --where post_name like 'S_1%';

  
select distinct tbl.geo.SDO_SRID  from MILE_MARKERS_IN tbl; --26916

insert into user_sdo_geom_metadata (table_name, column_name, diminfo, srid
 ) values ('MILE_MARKERS_IN',  'GEO',  
   sdo_dim_array(
      sdo_dim_element('X', 400000,  700000, 0.005),  
      sdo_dim_element('Y',4200000, 4700000, 0.005)
   ), 26916); --SRID


COMMIT;
CREATE INDEX MILE_MARKERS_IN_idx ON MILE_MARKERS_IN(GEO) INDEXTYPE IS MDSYS.SPATIAL_INDEX;   
   
   

XE Locator

Even the free Oracle XE edition includes Oracle Locator, which provides basic mapping functionality.

So you can download a sample Shape file from ESRI

Then you can download the standalone Map Builder java app from Oracle's website.
Then you can run the map builder app and import the shapefile as a base map or as a geometry theme.

Then you can download the standalone Map Viewer java web app (war file) from Oracle's website.
I ran the simple war file in my Apache tomcat server.

Then you can write a simple html page using the new 12c javascript V2 API. (put the file in your MapViewer web root)

Then you can view the map and or theme in your browser.

You can also filter just like you could with simple sql "where" predicate record selection.

See below, I am filtering based on county name.  (any county name which starts with He%)



--show bouding boxes for various geo table_columns
SELECT dim.*, usgm.*
  FROM user_sdo_geom_metadata usgm,
  TABLE(usgm.diminfo) dim
WHERE table_name = 'COUNTY';


--is my geo col data valid?
select case 
  when (tbl.geometry.st_isvalid()=1) then 'valid' 
  when (tbl.geometry.st_isvalid()<>1) then 'invalid' 
end valid_flag, tbl.* from SPMS tbl;


---------------------
Indiana location info using SRID=26916:
y      4,400,000 4180946.6978492998 to 4625448.000535274
x        550,000  403443.0718977172 to  692180.1995906243
height   500,000

Create table and spatial index 

CREATE TABLE COMMUTERCORRIDORS(
  "OBJECTID" NUMBER(*,0) NOT NULL ENABLE,
"ROUTE_ALIAS" NVARCHAR2(255),
"RP" NUMBER(15,6),
"END_RP" NUMBER(15,6),
"SPEED_LIMIT" NUMBER(15,6),
"SHAPE" "MDSYS"."SDO_GEOMETRY"
);

insert into user_sdo_geom_metadata (table_name, column_name, diminfo, srid
 ) values ('COMMUTERCORRIDORS',  'SHAPE',
   sdo_dim_array(
      sdo_dim_element('X', 400000, 692000, 0.005),
      sdo_dim_element('Y',4200000, 4630000, 0.005)
   ), 32039);

CREATE INDEX COMMUTERCORRIDORS_idx ON COMMUTERCORRIDORS(shape) INDEXTYPE IS MDSYS.SPATIAL_INDEX;

==================================================================
SDO_UTIL.VALIDATE_WKBGEOMETRY
Validates the input geometry, which is in the standard well-known binary (WKB) format;
returns the string TRUE if the geometry is valid or FALSE if the geometry is not valid.

SDO_UTIL.VALIDATE_WKTGEOMETRY
Validates the input geometry, of type CLOB or VARCHAR2 and in the standard well-known text (WKT) format;
returns the string TRUE if the geometry is valid or FALSE if the geometry is not valid.


==================================================================

--are my geometries valid?

select case
  when (tbl.geometry.st_isvalid()=1) then 'valid'
  when (tbl.geometry.st_isvalid()<>1) then 'invalid'
end valid_flag, tbl.* from SPMS_INDOT tbl;

--get lat/lon of each vertices in polygon!!!

select name,
   geo_tbl.x longitude,
   geo_tbl.y latitude
from county,
table (sdo_util.getvertices(geometry)) geo_tbl
where name like '%Hendric%';

--show bounding box (min/max x/y of geo table column)

SELECT dim.*
  FROM user_sdo_geom_metadata usgm,
  TABLE(usgm.diminfo) dim
WHERE table_name = 'DISTRICTS';



location info using SRID=26916:
y      4,400,000 4180946.6978492998 to 4625448.000535274
x        550,000  403443.0718977172 to  692180.1995906243
height   500,000



 400000 to  692000
4200000 to 4630000

--do this BEFORE CREATE INDEX myIdx ON myTbl(MyGeoCol) INDEXTYPE IS
insert into user_sdo_geom_metadata (table_name, column_name, diminfo, srid) values
 ('myTblNAME','myGeoCol',
  sdo_dim_array(--bounding box min/max x/y
    sdo_dim_element('X', 400000, 692000, 0.005),
    sdo_dim_element('Y',4200000,4630000, 0.005)),
  32039); --SRID



====================
--list all spatial indexes
select INDEX_NAME from USER_SDO_INDEX_INFO

--show the srid of an sdo_geometry column
select tbl.shape.SDO_SRID from COMMUTER_CORRIDORS tbl;


==================================================================
insert into user_sdo_geom_metadata (table_name,column_name,diminfo,srid) values
 ('TABLE_NAME','GEOM_COLUMN_NAME',
  sdo_dim_array(
    sdo_dim_element('X',80000,100000,0.005),   --bounding box x range 80000 to 100000
    sdo_dim_element('Y',425000,450000,0.005)), --bounding box y range 425000 to 450000
  32039); --SRID

...where the X, Y values define the
     min bounding box of spatial data.

ex:
  minX=80000, minY=425000, maxX=100000, maxY=450000


update user_sdo_geom_metadata set SRID=26916 where TABLE_NAME='COMMUTER_CORRIDORS';




-------------------------------------------------------------------
-- SPATIAL QUERIES --
-------------------------------------------------------------------
-- get topological intersection of 2 geometries.
SELECT SDO_GEOM.SDO_INTERSECTION(c_a.shape_col, c_c.shape_col, 0.005)
   FROM cola_markets_tbl c_a, cola_markets_tbl c_c
   WHERE c_a.name = 'cola_a' AND c_c.name = 'cola_c';

-- Do 2 geometries have any spatial relationship?
SELECT SDO_GEOM.RELATE(c_b.shape_col, 'anyinteract', c_d.shape_col, 0.005)
  FROM cola_markets_tbl c_b, cola_markets_tbl c_d
  WHERE c_b.name = 'cola_b' AND c_d.name = 'cola_d';

-- all cola market areas
SELECT name, SDO_GEOM.SDO_AREA(shape_col, 0.005) FROM cola_markets_tbl;

-- cola_a area
SELECT c.name, SDO_GEOM.SDO_AREA(c.shape_col, 0.005) FROM cola_markets_tbl c
   WHERE c.name = 'cola_a';

-- distance between 2 geometries.
SELECT SDO_GEOM.SDO_DISTANCE(c_b.shape_col, c_d.shape_col, 0.005)
   FROM cola_markets_tbl c_b, cola_markets_tbl c_d
   WHERE c_b.name = 'cola_b' AND c_d.name = 'cola_d';

-- valid geometry?
SELECT c.name, SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT(c.shape_col, 0.005)
   FROM cola_markets_tbl c WHERE c.name = 'cola_c';

-- valid layer? (First, create the results table.)
CREATE TABLE val_results (sdo_rowid ROWID, result VARCHAR2(2000));

CALL SDO_GEOM.VALIDATE_LAYER_WITH_CONTEXT('cola_markets_tbl', 'shape_col', 'VAL_RESULTS', 2);

SELECT * from val_results;





--------------------------------
Get LINESTRING of points from route start and end
create or replace function Get_Geo_For_RouteSegment(srid number default null,
   routeName varchar2, start_mile number, end_mile number)
   return sdo_geometry is
  returnedLineStringGeo        sdo_geometry;
  curr_pos  number;
    /*************************************************************************
       Author....: Michael Dockery
       Purpose...: Build linestring (2002) geometry object from a range of points (from a route section)
       Usage.....: select Get_Geo_For_RouteSegment(26916,'I-69','SOUTH',10,20) from dual;
     **************************************************************************/
begin
  returnedLineStringGeo := sdo_geometry(2002, srid, null, sdo_elem_info_array(1,2,1),   sdo_ordinate_array()  );
  curr_pos := 1;

  for mmGeoPointCursor in ( select geo from stg_mile_markers_in
    where ROUTE_ALIAS=routeName and mm between start_mile and end_mile order by mm) loop
    returnedLineStringGeo.SDO_ORDINATES.extend(2);
    returnedLineStringGeo.SDO_ORDINATES(curr_pos)   := mmGeoPointCursor.geo.sdo_point.x;
    returnedLineStringGeo.SDO_ORDINATES(curr_pos+1) := mmGeoPointCursor.geo.sdo_point.y;
    curr_pos := curr_pos +2;
  end loop;

  return returnedLineStringGeo;
end;




Publish a shapefile or GeoJSON file

http://doc.arcgis.com/en/arcgis-online/share-maps/publish-features.htm#ESRI_SECTION1_49CE0570C3BA4AD8BF2DB28929FF7280



To web-enable feature layers from shapefiles (packaged in a .zip file)
 or GeoJSON files (.geojson or .json),
 upload your files to Esri's cloud
  and have them hosted as services.

These services are referred to as "hosted feature layers"

This is a useful workflow
 if you do not have any ArcGIS products installed locally.

if you are signed in and that you have privileges to create content
Open My Content-> Add Item -> From my Computer.
  browse/select the file on your computer.
   then click Open.

 Check "Publish this file as a hosted layer"
  assign a title, tag terms separated by commas.
   Click Add Item.

Note:

 Shapefiles must be compressed as a .zip file containing at least the
.shp,
.shx, and
.dbf files.

 If no .prj file and the x,y domain extent is between -180 and 180 degrees,
   the features will be published in the GCS_WGS_1984 coordinate system.

 If there are multiple shapefiles in the .zip file,
   they will all be included in the feature layer.

ESRI + Oracle

 a couple of the relevant latest esri arcgis desktop screens. 



If you are the db & geodb admin, use the "Create Enterprise Geodatabase geoprocessing" tool (or Python script)
  to easily create a geodb in Oracle, cuz it automatically:
  Creates a database user named sde
  Creates a 400 MB tablespace in the Oracle default location and sets it as the sde user's tablespace
  Grants the sde user privileges to create & upgrade a geodb, remove db connections & import data via Data Pump
  Grants package privileges required to create a geodatabase
  Creates a geodatabase


Import file geodatabase



"...With the release of ArcGIS 10.3 comes the end of support for the ArcSDE service and the application server connections (three-tier).
Additionally, ArcSDE command line tools have been replaced by geoprocessing tools in the ArcGIS clients. Therefore…"

http://cehelp.esri.com/help/index.jsp?topic=/com.procedural.cityengine.help/html/manual/import/import_overview.html

http://cehelp.esri.com/help/index.jsp?topic=/com.procedural.cityengine.help/html/manual/import/fgdb.html

The ESRI File Geodatabase (FileGDB) is a file-based database for vector and raster data.
It can be identified as folder with the suffix .gdb. For example, myDatabase.gdb

Import via menu can be started by
  Right-mouse > Import... on a file in the Navigator,
  or via menuFile > Import...In contrast to Drag and Drop import,
   each import format will present a file dialog with additional options.

There is a "FileGDB inspector" which lists the layers inside the gdb file.

"...the upper part of the wizard page shows the layers available for import. These are the available columns in the FileGDB inspector:
Layer           Name of the Layer. The checkbox will decide if the layer is actually imported.
Type            If the layer is a feature class, its geometry type will be displayed. Else, the layer type will be displayed.See below for a list.
Count           If a feature class or a table, the number of rows will be displayed.
Readable?       A layer is readable (= importable), if it is a feature class with non-zero row count and has a supported coordinate system. If you hover the mouse pointer over the  sign, a tooltip will tell you the reason for the layer not being readable.
CS Authority    The "CS Authority" column displays the coordinate system EPSG authority ID.
CS Description  This displays the coordinate system description.

The following layer types are supported:
Point
Polygon
Polyline
Multipatch (with textures)
Table (indirectly, if connected via relationship classes)
Relationship Classes (indirectly)

Each FileGDB layer with geometry (= also called a "Feature Class") 
 is imported as a separate CityEngine layer. Layer types that are not supported are marked with the  sign, and will not be imported. ..."

Arc Catalog  - python script to export feature class to shape file


#**************************************************
# Export_FeatureLayers.py
# author: mdockery
# purpose: copy feature classes to shapefiles (via FeatureClassToGeodatabase).  
#          Convert to 2D by removing Z and Measures
# Usage: C:\Python27\ArcGIS10.5\python.exe Export_FeatureLayers.py
#************************************************** 
import arcpy
from arcpy import env
arcpy.env.outputCoordinateSystem = arcpy.SpatialReference(4326)
arcpy.env.outputMFlag = "Disabled"
arcpy.env.outputZFlag = "Disabled"
arcpy.env.workspace = "Database Connections\\RAHP_as_RAHDW.sde"


arcpy.FeatureClassToShapefile_conversion(["RAH.UrbanArea"],           "C:/temp/RAH")
arcpy.FeatureClassToShapefile_conversion(["RAH.MPA"],                 "C:/temp/RAH")
arcpy.FeatureClassToShapefile_conversion(["RAH.COUNTYBOUNDARY_EVW"],  "C:/temp/RAH")
arcpy.FeatureClassToShapefile_conversion(["RAH.DISTRICTPOLY"],        "C:/temp/RAH")
arcpy.FeatureClassToShapefile_conversion(["RAH.LRSE_SPEED_LIMITS"],   "C:/temp/RAH")
arcpy.FeatureClassToShapefile_conversion(["RAH.LRSE_NHS"],            "C:/temp/RAH")
arcpy.FeatureClassToShapefile_conversion(["RAH.LRSE_REFERENCE_POST"], "C:/temp/RAH")


Map Builder



rem ******************************************************
rem author mdockery
rem purpose import a shape file to oracle db 
rem ******************************************************
java -cp %ORACLE_HOME%\jdbc\lib\ojdbc8.jar;%ORACLE_HOME%\md\jlib\sdoutl.jar;%ORACLE_HOME%\md\jlib\sdoapi.jar ^
 oracle.spatial.util.SampleShapefileToJGeomFeature ^
-h orap05vw -p 1547 -s dbdw -u usr -d pwddd ^
-t STG_RAH_DISTRICT_POLY ^
-f \\trainingpc\temp\RAH\DISTRICTPOLY

Comments

Popular Posts