This discussion is archived
11 Replies Latest reply: Dec 13, 2012 9:25 AM by 824871 RSS

Import Shape File to Oracle 11g R2 with Projection automatically.

823645 Newbie
Currently Being Moderated
Hi all,

Can any one have the tool to import the shape file with projection into Oracle 11g R2 on Windows. I have seen so many tools to import the shpe file without projection (ie. I need to enter the SRID if I want Projection). But I want to import the shape file with projection from .PRJ file directly without asking any SRID. If you have any code to create a custom srid, please provide it.

waiting for your reply...
  • 1. Re: Import Shape File to Oracle 11g R2 with Projection automatically.
    Simon Greener Journeyer
    Currently Being Moderated
    The problem with shapefiles is whether the PRJ file is included. If it is included, some software products can read it, others can't.

    But most shapefile loaders usually have the ability to specify the SRID manually either on the command line or in a dialog.

    Tools you could try are:

    * ogr2ogr
    * Oracle's shp2sdo
    * Oracle's MapBuilder
    * GeoRaptor, the open source SQL Developer extension that has a shapefile loader with the ability to pick the SRID.
    For GeoRaptor, a new release is imminent. If you wish to try the Early Access release go to our Sourceforge website
    at http://sourceforge.net/projects/georaptor/files/georaptor/early-access/org.GeoRaptor-install.jar/download or just
    go to the website and download the current release.
    * FME

    regards
    Simon
  • 2. Re: Import Shape File to Oracle 11g R2 with Projection automatically.
    823645 Newbie
    Currently Being Moderated
    Hi Simon,

    Thanks for your prompt reply. I have used some of the tools you have mentioned in your reply, But there I need to give the SRID manually. To give the SRID manually I need to verify the corrosponding SRID. So I need a tool which automatically takes the Corrosponding SRID if available otherwise it will create a new srid and this ID will be used. If there is any tool which gives the corrosponding SRID by taking the .PRJ file content, Kindly let me know.

    Thanks & Regards
    Gouse
  • 3. Re: Import Shape File to Oracle 11g R2 with Projection automatically.
    Simon Greener Journeyer
    Currently Being Moderated
    Gouse,

    'tis a tall ask my friend.

    From my research quite a few of the open source tools eg GeoTools, ogr and QGIS can, supposedly, read the PRJ file. But to map the contents to a particular Oracle SRID? That I don't know (and doubt).

    These open source tools try to map the contents of the PRJ file to PROJ4 or an EPSG SRID. If the EPSG SRID is the same as Oracle's then you are OK, but I doubt if any of those tools actually check
    the Oracle metadata to see if that is the case.

    I understand why you would want a new Oracle SRID created if a match cannot be found but no tool does this AFAIK. I have suggested the idea of putting in create SRID functionality
    into GeoRaptor but all current members of the project agree that it is not worth the effort compared to the other functionality that should be in it (as it is not a common thing that is wanted).

    One problem practitioners don't understand is this. The PRJ's WKT structure is controlled by a standard. But the actual strings that describe the elements were not covered in that standard.
    For example here is an ESRI generated SRID:
    GEOGCS["Australian GDA94 Latitude/Longitude, Degrees",DATUM["D_GDA_1994",SPHEROID["Geodetic_Reference_System_of_1980",6378137,298.2572221009113]],PRIMEM["Greenwich",0],UNIT["Degree",0.017453292519943295]]
    But there are a number of Oracle WKTEXT equivalents, two of which are:
    4283 GEOGCS [ "GDA94", DATUM ["Geocentric Datum of Australia 1994 (EPSG ID 6283)", SPHEROID ["GRS 1980 (EPSG ID 7019)", 6378137.0, 298.257222101]], PRIMEM [ "Greenwich", 0.000000 ], UNIT ["Decimal Degree", 0.0174532925199433]]
    or
    8311 GEOGCS [ "Longitude / Latitude (GDA 94)", DATUM ["GDA 94", SPHEROID ["GRS 80", 6378137, 298.257222101]], PRIMEM [ "Greenwich", 0.000000 ], UNIT ["Decimal Degree", 0.01745329251994330]]
    With the first being more likely as this is the EPSG definition.

    As you can see the element names are different as there is no standard dictionary or lexicon that has been promoted by a standards body. And, some of the values don't quit match as strings but would as numbers eg Decimal Degree.

    I could create such a matching tool but I don't get paid for GeoRaptor development and it wouldn't want to spend weeks creating a tool only one or two people might use.

    So, my friend, that is why things are poorly supported. Oracle's move to support EPSG at 10g was welcome. But what would help integration is a standard lexicon of names that all companies agreed to use....

    regards
    Simon
  • 4. Re: Import Shape File to Oracle 11g R2 with Projection automatically.
    823645 Newbie
    Currently Being Moderated
    Hi Simon,

    Thanks for your valuable reply. I am facing the problem as explained by you. Currently I have a GIS application which supports the Importing of Shape File into PostgreSQL/PostGIS with projection automatically (If projection is already available that SRID will be used else new SRID will be created). Now I would like to extend this functionality to Oracle Spatial also. So I have raised this Question. Could you please suggest me the approach (steps/flow chart) of doing this.

    Thanks & Regards
    Gouse
  • 5. Re: Import Shape File to Oracle 11g R2 with Projection automatically.
    Paul Dziemiela Journeyer
    Currently Being Moderated
    Hi Gouse (and Simon),

    Interesting topic to butt into. My initial thought is that your approach is not a good fit for Oracle Spatial. I believe most of us really cling tightly to the Oracle SRID system and only generate new SRIDs as a last resort - a terrible, unhappy last resort. And when we do we all end up using 1000001! This is not a recipe for portability or mutual understanding. If someone sends me a data pump export of spatial data with SRID 1000002 I am just going to roll my eyes. I will need to know exactly what 100002 is on your system and then have my DBA install it on mine as an available SRID number (including the custom ellipsoids, datums, ops, etc) and then convert the export to the new SRID. That's a tall order. And passing it along to next person starts the mess all over again. I think most of us would go out of our way to avoid that.

    On the other hand the type of system you are spec'ing is perfectly reasonable as that's pretty much what ESRI's ArcSDE product does. Every time you import SDE data into Oracle as the SDE.ST_GEOMETRY data type (note this is not MDSYS.ST_GEOMETRY), the client software checks the SDE metadata for an existing SDE ST_SRID that matches your cs. If it find anything less than a perfect match, it will generate a new SDE ST_SRID and your imported data will get that new number. The ESRI system goes beyond WKT specifications to include envelope, scale, offsets and several other parameters you would not normally consider part of the cs but are needed by the ESRI's storage mechanism. So when using SDE.ST_GEOMETRY my SRIDs are pretty much meaningless. I have a dozen SRIDs on one machine representing 4269 NAD83 (usually different envelopes) and a dozen more on my second machine and dozens more in production etc - none of them the same. So using anything but ESRI proprietary tools to move this data about is difficult as you need the logic to map SRID X on machine A to SRID Y on machine B. ESRI does that for you - datapump, database links, sql loader of course do not. (Yes, there are ways to clone the entire SDE setup from machine to machine but that's another whole order of magnitude of maintenance.) But the point is the ST_SRID is meaningless without looking up in the metadata tables what it really means.

    So I suppose I am not entirely thrilled by the prospect of your application auto generating new SDO_SRIDs every time someone fails to include the .prj file with a shapefile. I don't really think that's what a typical user would want (feel free to argue!). I would say you really WANT to stop and look at the shapefile, query the source and determine the best existing - hopefully universal (in the small Oracle Spatial sense) - SRID. Perhaps there is no reasonable SRID in your system and you do need a custom SRID, but that should be a last, sad, head-hanging-low resort.

    So I mentioned that ESRI's own storage type goes your route but then they control the whole SDE.ST_GEOMETRY metadata system. A good question might be how does ESRI handle the Oracle Spatial side of mapping their coordinate systems to Oracle Spatial when you choose that storage option. They can't control the MDSYS tables anymore than any of us can. Oracle can and does change things and users can and do change things. So how does ESRI go about the tricky business of deciding on the appropriate SDO_SRID? They don't even try. :) Just like Simon and georaptor do, you just pick an SRID and hope (pray) that the data fits (technically you hardcode the SRID into the dbtune keyword control file and then choose the keyword you want but its basically the same as picking an SRID). Perhaps some other folks familiar with other proprietary and open source products could weigh in with how others do it, perhaps its easier than I think.

    I did have the stray thought if this actually is a good approach for PostGIS? Would your typical PostGIS spatial user want all kinds of custom SRIDs bouncing about any more than Oracle users would?

    Anyhow, interesting topic. I don't mean to get the thread off-track as I am sure Simon is hard at work on those flowcharts!! :)

    Cheers,
    Paul
  • 6. Re: Import Shape File to Oracle 11g R2 with Projection automatically.
    Simon Greener Journeyer
    Currently Being Moderated
    Paul,
    I am sure Simon is hard at work on those flowcharts!!
    Flowcharts? Tell me more!

    My point to Gouse is that no one has a mapping from any ESRI PRJ file to Oracle SRID as part of any load tool.

    You comments about ESRI ArcSDE are on the money. But they don't create SRIDs in the Oracle metadata they match and create in their own.

    The need to create a new Oracle SRID is rare. I've only been involved with it twice in 15 years.

    One way to create a load tool for the PRJ file (and I have seen PRJ files that display names from a non-ESRI lexicon of values) is to rely on:

    1. The common structure of the WKT;
    2. The values as against the tag names.

    Then, might be possible is to take an ESRI PRJ file, strip it of its non-standard tags, and then use those tag values to search the WKTEXT (suitably stripped) of the cs_srs until one gets a match on the values.

    I might have a look at how possible this is but I am a bit busy at the moment on other things. Here's an example:
    select c.wktext
      from cs_srs c
     where srid = 4283
    union all
    select REPLACE( listagg(t.token || t.separator) within group (order by id),' ','') as wktext
      from cs_srs c,
           table(Tokenizer(REPLACE(
                           REPLACE(
                           REPLACE(c.wktext,' [ ','['),
                           '], ','],'),
                           '", ', '"'),
                           '",[]')) t
     where srid = 4283
       and t.separator <> '"';
    -- Results
    --
    WKTEXT
    -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    GEOGCS [ "GDA94", DATUM ["Geocentric Datum of Australia 1994 (EPSG ID 6283)", SPHEROID ["GRS 1980 (EPSG ID 7019)", 6378137.0, 298.257222101]], PRIMEM [ "Greenwich", 0.000000 ], UNIT ["Decimal Degree", 0.0174532925199433]]
    GEOGCS[DATUM[SPHEROID[6378137.0,298.257222101]],PRIMEM[0.000000],UNIT[0.0174532925199433]]
    regards
    Simon

    Edited by: Simon Greener on Dec 13, 2012 2:43 PM
  • 7. Re: Import Shape File to Oracle 11g R2 with Projection automatically.
    Stefan Jager Journeyer
    Currently Being Moderated
    Simon,

    Very interesting idea and code. An addition might be to look for the acronym "EPSG", and check if there is a 4- or 5-digit number close behind (within the ""). If you find something there you may be able to match that to a SRID.

    On the whole and on topic: I agree with Paul completely: do not create your own SRID. In the decade I've been working with Oracle Spatial, I've never needed to use aything but what Oracle delivered, and have encountered quite a few custom SRID's which only caused tremendous data problems (which would usually be solved once migrated to a 'standard' SRID).

    What would be a very nice-to-have is if ESRI would use the EPSG-ID in their project files. Even nicer-to-have would be if the EPSG-group would offer a webservice where you could retrieve all parameters for a CS from, so that there would be no need to store the parameters, just the ID. But from experience I know that to be a utopia :-)

    What might help in matching PRJ-files and SRID WKT's, is the Semantic stuff in Oracle (which also happens to be in MDSYS). I've never really played with it, but maybe I should change that ;-)

    Cheers,
    Stefan

    Edited by: Stefan Jager on Dec 13, 2012 10:28 AM
  • 8. Re: Import Shape File to Oracle 11g R2 with Projection automatically.
    Simon Greener Journeyer
    Currently Being Moderated
    Stefan,
    Very interesting idea and code. An addition might be to look for the acronym "EPSG", and check if there is a 4- or 5-digit number close behind (within the ""). If you find something there you may be able to match that to a SRID.
    You have to handle the situation where the a WKTEXT entry contain the same EPSG ID values as this shows:
    select distinct srid, 
           TO_NUMBER(REPLACE(t.token,'EPSG ID ','')) as token
      from cs_srs c,
           table(Tokenizer(c.wktext,'()')) t
     where wktext like '%EPSG ID%' 
       and t.token like 'EPSG ID%'
    order by 2, 1;
    -- Results (Returns 6008 rows on 11gR2 so show only a few)
    --
    SRID TOKEN
    ---- -----
    3821 1025
    3827 1025
    3828 1025
    3823 1026
    3824 1026
    3825 1026
    3826 1026
    4001 6001
    4002 6002
    4003 6003
    4004 6004
    4005 6005
    4006 6006
    4007 6007
    4008 6008
    4009 6009
    .....
    Interestingly....
    select distinct srid, 
           TO_NUMBER(REPLACE(t.token,'EPSG ID ','')) as token
      from cs_srs c,
           table(Tokenizer(c.wktext,'()')) t
     where wktext like '%EPSG ID%' 
       and t.token like 'EPSG ID%'
       and TO_NUMBER(REPLACE(t.token,'EPSG ID ','')) = srid
    order by 2, 1;
    -- Results
    --
          SRID      TOKEN
    ---------- ----------
         53004      53004
    There is probably a better way to link EPSG IDs and Oracle SRIDS via queries against the actual MDSYS CS metadata tables.

    (The Tokenizer function is available in a blog article on my website.)
    On the whole and on topic: I agree with Paul completely: do not create your own SRID.
    We all agree.
    What would be a very nice-to-have is if ESRI would use the EPSG-ID in their project files.
    Yes it would be nice but ESRI will never do it - it is not in the DNA. After all, they don't even want their customers to use anything but SDE.ST_GEOMETRY in Oracle!
    Even nicer-to-have would be if the EPSG-group would offer a webservice where you could retrieve all parameters for a CS from, so that there would be no need to store the parameters, just the ID.
    www.spatialreference.org should offer such a webservice.
    What might help in matching PRJ-files and SRID WKT's, is the Semantic stuff in Oracle (which also happens to be in MDSYS).
    What do you have in mind?

    regards
    Simon
  • 9. Re: Import Shape File to Oracle 11g R2 with Projection automatically.
    Stefan Jager Journeyer
    Currently Being Moderated
    Simon,
    What do you have in mind?
    This is kind of hijacking this thread and taking it off-topic, but about one, maybe two years ago I attended a meeting from the Oracle Users club Holland (OGH) about semantics. It was very interesting, and has been on my list of things to dive into (to satisfy my own curiosity, not because of commercial needs or so - which has never really happened unfortunately). I seemed to remember something about semantic searches ( a bit more fuzzy searching, I suppose), but a quick check of the documentation does not reveal anything that could be useful here I think.

    I think I'll have a look tonight to see if there is an easy way of matching CS'ses to SRID's, I find it an intriguing idea that might solve a lot of data import and export issues.

    Cheers,
    Stefan
  • 10. Re: Import Shape File to Oracle 11g R2 with Projection automatically.
    823645 Newbie
    Currently Being Moderated
    Hi Simon, Paul and Stefan

    Thanks for your views. As a developer I don't want my users to stick to the SRIDs provided by the Oracle (Default) and there might be requirement of Custom coordinate reference system. Of course, all of users don't required this but some of them required, may be 1 or 2 users but still my application should support them and I don't think Oracle has the SRID's for all the Coordinate Systems using in the world. One more thing majority of the users don't know how to find the corresponding SRID to their WKTEXT(EPSG) or .PRJ file contents. So it is not a good idea to select the SRID manually by them. There are chances of selecting the wrong SRID. So I thought to find the Corresponding SRID automatically if available otherwise create a new SRID.
    I need all of your help in achieving this.

    Thanks & Regards
    Gouse
  • 11. Re: Import Shape File to Oracle 11g R2 with Projection automatically.
    824871 Newbie
    Currently Being Moderated
    Answering Paul's question:
    Perhaps some other folks familiar with other proprietary and open source products could weigh in with how
    others do it, perhaps its easier than I think.
    Since the library GDAL/OGR is widely used in many free-open-source, commercially-supported-open-source and commercial products, it might be worthwhile to see how it handles the problem.

    The GDAL/OGR "ESRI Shapefile" driver will try to parse the text from the .PRJ and create a OGRSpatialReference object.

    Locking at the documentation of the function that does that we will notice that it tries to keep up with old and new style of PRJ files.

    [OGRSpatialReference::importFromESRI|http://www.gdal.org/ogr/classOGRSpatialReference.html#af91af2639702e2793daf93ffe231b577]
    This function will read the text loaded from an ESRI .prj file, and translate it into an OGRSpatialReference definition.
    This should support many (but by no means all) old style (Arc/Info 7.x) .prj files, as well as the newer
    pseudo-OGC WKT .prj files. Note that new style .prj files are in OGC WKT format, but require some
    manipulation to correct datum names, and units on some projection parameters. This is addressed
    within importFromESRI() by an automatical call to morphFromESRI().

    Currently only GEOGRAPHIC, UTM, STATEPLANE, GREATBRITIAN_GRID, ALBERS, EQUIDISTANT_CONIC,
    TRANSVERSE (mercator), POLAR, MERCATOR and POLYCONIC projections are supported from old style files.
    The OCI driver will receive the OGRSpatialReference object and will check for an EPSG code to be used as the SRID. If EPSG is not there, the OCI driver will try to add a new "user-defined" SRID, for example 1000003006. Next time, when more of that same data set is loaded, even though the PRJ are identical, the SRID will be probably 1000003007 and so on and as long as the user has privileges to add it. Bad idea.

    So it might the case that your Shapefiles is from an old version of ArcXXX. I have a similar case, one shape file with a very simple reference system that gives me that error when I try to load using ogr2ogr:
    ogr2ogr -f oci oci:USR/PWD@DB cities.shp
    ERROR 1: ORA-01031: insufficient privileges
     in INSERT INTO MDSYS.CS_SRS (SRID, WKTEXT, CS_NAME)  VALUES (1000003006,
    'GEOGCS["Longitude / Latitude (WGS 84)",DATUM["WGS 84",SPHEROID["WGS_84",
    6378137,298.257223563]],PRIMEM["Greenwich",0.000000],UNIT["Decimal Degree",
    0.0174532925199433]]', 'Longitude / Latitude (WGS 84)' )
    If I replace the content of the cities.prj by the new ESRI WKT [http://spatialreference.org/ref/epsg/4326/esriwkt/] I still get the same error but if I replace by the official OGC WKT with EPSG codes [http://spatialreference.org/ref/epsg/4326/ogcwkt/] everything works just fine and my sdo_geometry SRID is 4326.

    I am not sure that the problem is that the ESRI WKT doesn't have the EPSG authority code or that the GDAL/OGR/"ESRI Shapefile" driver should have added the Authority code to the OGRSpatialReference object.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points