Transforming non-spatial Oracle data to spatial SQL Server data

In this demo, we have a non-spatial Oracle database that contains coordinate information in two columns. You'll see how to use FME to convert this non-spatial data into a spatial SQL Server dataset. The primary key and other important attributes are extracted, and the coordinate values are transformed into points. We then reproject the points so they're in the correct coordinate system. The results can be previewed in SQL Server 2008.

You'll also see how to execute a SQL statement before or after a translation.

Transformers used: AttributeCopier, SchemaMapper, 2DPointReplacer, CoordinateSystemSetter

---

Closed Caption:

I'm a single mom thing I'm really
showing this workspace month
is I'm going from non spatial data to
special date
I have another and bookmark here where
we have some non spatial information
sorry my Oracle Database I happen to
have
hat and after strike Stephen here and we
take a look at the columns %uh he is
known
georgine here it's cool just I'm bachata
numbers and stuff like that yeah but
what we do have in years a couple
columns that are
wooden pallets not mix in some why did
not yeah
this is very common people think that
point the leaders geometry when really
all it's got
its yeah a single call are double column
X&Y
that mall and then they want to get into
spatial database or view
special tools you can't do that while
its
looking like this my I'm people have
these kinder
I'm options in spreadsheets you know
excelled a very common format even just
text files or your enterprise database
so what we want to do with this
particular table is I
we've been tasked to take it into you
sequels special summit
we've added yet another writer into our
workspace
I'm just gonna mean a police baton
article reprints are you reading from
our call and this time where writing to
you
she spatial inlets I my
as password up so that we've got good
stuff happening
passwords and you'll notice that when we
do this kinda linking they've given us
a table in the sequel so speech database
doesn't look is much like how
sources we thought it was going to be so
this lot to that which the
not going to be showing up in at table
the first thing you notice we got a few
red what's being so this table would
have been
borden by using net import from yeah
the database F so that that actually
come here and you get these
attributes joined up
we can take over things like our primary
index
we can keep pulled out that article
table and call it how
address ID you now sequel table and
again
this is very common mapping scheme from
source to a destination
or injury now only from one format if I
want to map
say I wasn't hundreds the scissor and
transformer gonna drop down that could
rename a whole bunch about tributes may
be
I could use an Excel spreadsheet and to
do this mapping yeah there is actually
we have a transformer
equal schema macro which when you're
doing her
being translation like this you would
use for this sort of thing will
then you can read your wrapping from
some sort
external format CSB
or even a database backup 2013 back on
track to be named oh okay me
then gonna make sure we turn this stuff
into spatial data and we have a chance
transforming tool for doing that you
something that we can use to well
to innovate into two key points very
easy to take reach
I can stomach to the point replace the
transformer
brings out later down into here and
again we just need to give it the names
of those
attributes that are holding those values
for us yeah so he we've got our X who
wouldn't
and here we've got my wife would
and what if I just had addresses user
need you coding
yet there is actually you could have
done we passed of ass street name we
couldn't
candidate just reading together so that
we had a ballot street name and then we
have age
your curtain transformer so it could be
used yes we can use API
who'd you recording server enti air
the time he called the proxy you and
instead righty
those you yeah and then of course there
then
know the ABA's demanding the sequel
spatial a database
they want this especially in a place to
be in geography format because
sequel forced into geometry and you
don't agree
rights reset the writer aP to write to
the geography format so geography to be
clear some like around the world
format is not a lot that's right I'm and
so the coordinate system for that will
be the slatwall
a whole rack but when we looked at me to
that
be we just know that yes well next we
shouldn't look
let we can see that both XLI columns on
anything like what long bout try so what
we need to do before we can look that
day there is really protect the East
so that they're in right wouldn't just
you happen to know what the court system
is
I do whoever looks after the state was
good enough to tell me that the yeah
what the coordinate system
value is so I have a coordinate system
said transformer can throw in here
just set the coordinate system opposes
peaches as they come through
into a Texas a plane
83 dashed yet coordinate system
in with writes a nap it will reject it
from that who wouldn't just mean to you
that long
and output the data into a database
trust me doesn't automatically creates
the cable was lol 84 so it not just do
that
yeah and if you didn't know that
transform into cost you were
trial on when you went to add race that
weekend
we can see that that we take a look in a
sequence that the database
I'm not gonna run this week space that
will see that we've got our actors
points in there
and we take a look at them we can look
at the top
thousand roast on my first day back to
show you the top five thousand cuts
bit more interesting was a little bit
bigger
and Siebel Server 2008 has this great
special result so that you can check
you better actually is we said it was
going to be and so he you can see the
did ride in with the geography special
column and if you are
you mean a bitch I'm isn't juries week
it's even the
it's very hard to see on the screen
which is free all yeah
you can bring Jose baton creates
my so that's our first was based on that
guy who does not understand I'm
oh one last thing if course we got there
early and then the TPS
well you weren't supposed to bring in
the inactive addresses so how come you
didn't pick that up
so won't be slow yeah we hope gonna have
heard what we've got on there are
readers is the ability through wrinkles
Rican say I really want
particular pot this information so I
could see it
i rly want with status at this address
is equal is not equal see you know what
just
active ones and then that would have
narrowed down my search on the leader in
the beauty of that is that it puts the
work back into the database yes that
much faster for everybody
CBO sup yes so about and that's much
more efficient approach than reading
them all in and having a test filter
nafta me to do it can select the
database do its thing
that's right yeah and soaking taken so
that we can do things like a
put up primary he's in here we can
inexperienced what we've got a variety
of different options here
yeah which also brings out the fact that
this stunning
I'm actually it was actually linked to
another table my army certain values
designing and
I we want to make sure that that it
didn't break any foreign key constraints
but because we were doing a book ok
Lloyd we wanted to turn that off we
Brits
you know we designing table was a bit of
a date and we didn't want it to
I'll from about good data yeah wanted
other alternatives that's available on
many about reducing right this is the
ability to run
a sequels statement before you execute
the translation
shit and so what I was able to do you
want to not
Eastern checking yeah so we to know
we like the data and then I have to be
from the translation we can turn it back
on again
so we have people he hadn't run second
one taking my constraint back on
and again that capability so much to
this is available on all accounts a you
can do this on our cool you can do this
on post just
in your yeah
cool well so that's reading and writing
yes
pretty messed 100 s %uh yeah in pretty
much you wanted two things is and I
didn't have to know much about any
particular database el look
pretty much looks much the same if you
are familiar with databases
and you know article here we handle
Workspace Manager workspace management
some do that
I'm we've got a ride murder on here we
could updates or incidents whatever
right but one of many when you get your
arm
output is a yet the completed work space
and you take a look at these different
transformers if you didn't have to catch
no
great