Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

I have ArcSDE installed in a Postgresql database and I am using PostGIS geometry. I'm not a DB minded person so feel free to let me know if my process is not right.

I create my feature class in ArcCatalog (no probs) I then go into PgAdminlll to check out the new table. There are two things I notice when I check out my new table.

1) The record for my new table in the public.geometry_columns table has an SRID of -1. Is there any way to set the SRID during the feature class creation? If not how do I do this in PgAdminlll? Is it something like updateGeometry or addGeometry?

2) Also I notice that in PgAdminlll under the newly created tables properties there is no primary key assigned. So what I have been doing is assign the sde created objectid field as the primary key. Is this acceptable or should I be creating a new PK?

Thanks Ando

share|improve this question
add comment

3 Answers 3

up vote 2 down vote accepted

ad 1) If you view the contents of geometry_columns table (in PgAdmin3), you can simply double-click into the srid cell and type in any SRID you want. (If you have write access of course).

ad 2) Assuming that the SDE's objectid is kept unique and doesn't change, it shouldn't be a problem to use it as a primary key. I guess SDE doesn't create the tables with OIDs?

share|improve this answer
    
OMG I have never double clicked the cell. Thanks, I just assumed that it required some kind of SQL statement setting the SRID value! –  Ando Nov 25 '10 at 9:25
    
@Underdark - I went and manually changed the SRID in the Geometry_columns table and I am now receiving an error in qgis. For example I change the SRID from -1 to 28355 and then loaded it into qgis withe error. So then I took a layer that had an SRID defined example change a layer with 28355 to -1 and got the same error. Maybe there is dependencies else where which are causing this. –  Ando Nov 25 '10 at 20:53
    
In a default PostGIS database there are no other dependencies. I don't know what SDE adds. What error message do you get (I'd guess QGIS can't find usable unique id)? –  underdark Nov 25 '10 at 20:59
    
I wish I could just attach the dialog box here. It reads as follows. 1 cursor states lost. n/ SQL: CLOSE qgisf1 n/ Results: 7 (ERROR: current transaction is aborted, commands ignored until end of transaction block). I tried changing the SRID on a vanilla straight PostGIS layer (no interaction with SDE) and received the same error. –  Ando Nov 25 '10 at 21:06
1  
It has all worked out in the end. I have since created other layers through ArcCatalog and the SRID is entered into the geometry_columns table perfectly. The layers are viewable in QGIS and edits in either applications (QGIS are ArcInfo) are reflected. Perfect! Thanks for sticking with me Underdark, I appreciate the help and the news on you blog about QGIS 1.6 release woot woot! –  Ando Nov 29 '10 at 0:32
show 4 more comments

You can create Feature Classes in PostGIS using ArcGIS with the correct EPSG codes, with no need to correct the database.

Create a new folder under Projected Coordinates Systems, eg called epsg. Copy the original arcgis prj files into this folder. Rename the files to something meaningfull. Edit the files to reflect this name, and at the end of the line change the id to the epsg code.

Now, this epsg code will be available in Arcmap or arccatalog when creating a feature class. It will store the correct epsg code in the database too.

Example:

  • create folder C:\Program Files (x86)\ArcGIS\Desktop10.0\Coordinate Systems\Projected Coordinate Systems\epsg
  • copy file "Datum 73 Hayford Gauss IPCC.prj" into this new directory
  • change name to "Datum 73 Modified Portuguese Grid 27493.prj"
  • edit and change the name of the coord sys like this:
    PROJCS["Datum 73 Modified Portuguese Grid 27493",GEOGCS["GCS_Datum......
  • edit and change the srid code to the epsg code:
    ...,AUTHORITY["EPSG",27493]]

While creating the new feature class, when selecting the coord system, navigate to projected coordinate systems, the new folder "epsg" is visible and you can see your new coord sys. Select it. Voila. The fc is correctly created.

share|improve this answer
add comment

Registering a table containing an ST_Geometry column with ArcSDE

If the table does not contain any data and you register the table with ArcSDE but do not specify a valid SRID, the default SRID is used. For PostgreSQL and Oracle tables with ST_Geometry columns, the default is 0. The SRID 0 is present mainly for testing purposes; it does not actually assign a spatial reference. If you later try to add features that have a valid SRID other than 0, it will fail because the SRIDs are not the same. Once an SRID is assigned to a table, it cannot be changed; you have to delete the layer and re-create it with the correct SRID

http://help.arcgis.com/en/arcgisdesktop/10.0/help/index.html#//006z00000030000000.htm

share|improve this answer
    
Thanks but i'm not registering a table with st_geometry. I have created my feature class in ArcCatalog, with PG_Geometry type. In the ArcGIS clients everything looks good (in terms of primary keys and spatial reference systems)! However you view the same table in QGIS and there is no Primary Key or SRID. What I want to know is how can I specify the SRID for my QGIS clients? and if the process I described above for establishing the primary key is sufficent? –  Ando Nov 25 '10 at 5:36
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.