Skip to main content
Question

finding a new coordinate for an oracle geometry from a CSV file through FME

K
Forum|alt.badge.img

Hello everyone,

We are doing a prototype where the geometries from Oracle spatial are imported to GE smallworld and are adjusted to new locations. The old and new locations are maintained in a CSV file and the source Oracle data is to be modified based on this CSV file using FME. Each and every coordinate in the Oracle spatial data will have it's original geometry and the adjusted geometry in the CSV file. I wanted to know if I can use FME to update all the oracle spatial coordinates with the new coordinates from CSV file. If I can, how to achieve this.

Appreciate any pointers and help in this.

Thanks,

Kalyan

12 replies

fmelizard
Contributor
Forum|alt.badge.img+17
  • fmelizardContributor
  • Contributor
  • September 18, 2017
Hi @kamepalli, do you have an ID in the CSV file that can be used to match features in the Oracle table?
steveatsafe
Safer
Forum|alt.badge.img+12
  • steveatsafeSafer
  • Safer
  • September 18, 2017
@kamepalli I might be reading into this too much... you mention "each and every coordinate". Are you dealing with more than just points here? Are you wanting to take, for example, a line feature and update some of the vertices but not all that make of that line feature (from the csv file)? Or is it just the an entire geometry that you'll be updating?

 

 

Tia's question is relevant either way.

 

If you have an ID in the CSV file than this should be trivial.

 

SteveatSafe
K
Forum|alt.badge.img
  • kamepalli
  • Author
  • September 18, 2017

@TiaAtSafe @SteveAtSafe thank you for your responses. Yes I can use a combination of fields to match features from both Oracle and the CSV fie. Steve, you are right in assuming that we would sometime change some of the vertices of a linear or polygon geometries but not the whole geometry. Is it still a possibility ?

Thank you for your time.

pratap
Contributor
Forum|alt.badge.img+11
  • pratapContributor
  • Contributor
  • September 19, 2017

Yes, updating geometries in oracle spatial can be done when you have same ID in both CSV and Oracle spatial data either with normal update statement in sql executor or other methods...

fmelizard
Contributor
Forum|alt.badge.img+17
  • fmelizardContributor
  • Contributor
  • September 20, 2017
pratap wrote:

Yes, updating geometries in oracle spatial can be done when you have same ID in both CSV and Oracle spatial data either with normal update statement in sql executor or other methods...

@kamepalli, I would try using the SQLExecutor as @pratap has suggested. The Oracle Writer itself can be used to perform updates as well (you can set whether to update spatial columns or not.
K
Forum|alt.badge.img
  • kamepalli
  • Author
  • September 20, 2017
fmelizard wrote:
@kamepalli, I would try using the SQLExecutor as @pratap has suggested. The Oracle Writer itself can be used to perform updates as well (you can set whether to update spatial columns or not.
@TiaAtSafe I am a beginner and I am not sure about too many options in FME. I've come across a post where a changedetector is used to pick up the changes and update destination. Which is a better choice ? SQL executor or a Changedetector ? also, would you help me point to some references?

 

K
Forum|alt.badge.img
  • kamepalli
  • Author
  • September 20, 2017
kamepalli wrote:
@TiaAtSafe I am a beginner and I am not sure about too many options in FME. I've come across a post where a changedetector is used to pick up the changes and update destination. Which is a better choice ? SQL executor or a Changedetector ? also, would you help me point to some references?

 

Thank you for those references @TiaAtSafe Having a sample workspace will help a lot.

 

fmelizard
Contributor
Forum|alt.badge.img+17
  • fmelizardContributor
  • Contributor
  • September 20, 2017

Hi @kamepalli, you should experiment with setting the Writer mode to update. I've used the OBJECTID as my Match Key. If you need a combination of attributes to create a unique key, you'll need to specify that.

Depending on how the data is actually stored, you can go about updating in different ways.

I'm not sure how the data is stored in your CSV, but the translation can be as easy as a Reader & a Writer.

Of course there are multiple ways to do this, this is just one of the ways.

K
Forum|alt.badge.img
  • kamepalli
  • Author
  • September 20, 2017
fmelizard wrote:

Hi @kamepalli, you should experiment with setting the Writer mode to update. I've used the OBJECTID as my Match Key. If you need a combination of attributes to create a unique key, you'll need to specify that.

Depending on how the data is actually stored, you can go about updating in different ways.

I'm not sure how the data is stored in your CSV, but the translation can be as easy as a Reader & a Writer.

Of course there are multiple ways to do this, this is just one of the ways.

Thank you @TiaAtSafe. I now have the data in a shape file so that the coordinates are now represented as a geometry itself. I will try to create a writer with a combination of columns as key and see how it goes.
steveatsafe
Safer
Forum|alt.badge.img+12
  • steveatsafeSafer
  • Safer
  • September 21, 2017
kamepalli wrote:

@TiaAtSafe @SteveAtSafe thank you for your responses. Yes I can use a combination of fields to match features from both Oracle and the CSV fie. Steve, you are right in assuming that we would sometime change some of the vertices of a linear or polygon geometries but not the whole geometry. Is it still a possibility ?

Thank you for your time.

@kamepalli . That isn't trivial to do. If you are really good with SQL and working with the SDO_GEOMETRY object you could possible do it... you'd have to know the ordinate array, the position of the vertex in the array and then you could do the update. I'm not well versed in this.

 

Here is an example of what I'm talking about where they are updating the X coord of an SDO_POINT structure...click here for article. You'll notice this refers to the SDO_POINT and this isn't the same for a polygon or linear feature. You might be able to find an example of this. I'll chat with a colleague to see if they have an idea of what it would look like in FME.

 

SQLExecuter will be your friend.

 

SteveatSafe
K
Forum|alt.badge.img
  • kamepalli
  • Author
  • September 21, 2017
steveatsafe wrote:
@kamepalli . That isn't trivial to do. If you are really good with SQL and working with the SDO_GEOMETRY object you could possible do it... you'd have to know the ordinate array, the position of the vertex in the array and then you could do the update. I'm not well versed in this.

 

Here is an example of what I'm talking about where they are updating the X coord of an SDO_POINT structure...click here for article. You'll notice this refers to the SDO_POINT and this isn't the same for a polygon or linear feature. You might be able to find an example of this. I'll chat with a colleague to see if they have an idea of what it would look like in FME.

 

SQLExecuter will be your friend.

 

Thank you very much for the info @SteveAtSafe I would have thought so. any pointers to this is greatly appreciated. So far I have tested point geometries and they seem ok. However, I used a shape file instead a CSV and using a simple writer with feature operation set to update. I will check this approach with linear geometries and see how it works.

 

 

However the field length of the shape file is being set to fixed 10 characters I have issues matching the columns. I will have to go for a mapinfo file and see if that solves this new issue.

 

K
Forum|alt.badge.img
  • kamepalli
  • Author
  • September 21, 2017
fmelizard wrote:

Hi @kamepalli, you should experiment with setting the Writer mode to update. I've used the OBJECTID as my Match Key. If you need a combination of attributes to create a unique key, you'll need to specify that.

Depending on how the data is actually stored, you can go about updating in different ways.

I'm not sure how the data is stored in your CSV, but the translation can be as easy as a Reader & a Writer.

Of course there are multiple ways to do this, this is just one of the ways.

Hi @TiaAtSafe as I notified in my previous comments I have now converted data from CSV to a shape file so that the geometry type conversions are not required. Now the geometry in shape and oracle are of same types. I used option 1 and created a reader to shape and a writer to oracle with a combined column identifier. So far so good. I've tested few records and the geometries updated to the ones in shape file.

 

 

I will do more updates and testing to see if this process works. Once again thank you for the help.

 

 

Reply


Helpful Members This Week

Community Stats

30,944
Posts
117,265
Replies
38,742
Members

Latest FME

Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.