Subscribe to this thread
Home - General / All posts - Qtn: Can you rename a drawing using only sql or assign a projection to a drawing using only sql?
tgazzard
146 post(s)
#29-Jul-14 02:26

Hi,

I do a lot of work using sql and a script to run multiple queries (which I got from this forum a while ago and has a huge difference to how I do things).

I have two questions:

1) Is it possible to rename a drawing using sql?

2) If you create a new drawing (using create drawing) it doesn't have a projection assigned to it. Is there a way to assign a projection using sql?

The two questions are somewhat related. In that it doesn't appear possible to rename a drawing. But I may be able to get around this by creating a new drawing and then inserting data from an existing drawing. However, there is no projection with the new drawing.

From reading the forum, it appears that the options are:

1) Using an options clause and then link to this drawing.

2) Write a script to assign the projection

What I was hoping to do was do all this in sql? Is this possible?

Thanks in advance

Tim

jkelly


1,234 post(s)
#29-Jul-14 06:45

Tim,

I do what you have mentioned, create a new drawing within the script, then via the script, create an insert query, build the query via the script as well, then run the query via the script. It's nice and fast.

As for creating the new drawing (through the script), you can certainly set your projection upon creation. It's one of the parameters.

My feeling, although I could certainly be wrong, is you are trying to do too much within SQL. Renaming a drawing is outside SQL scope, but is easily achieved through the object model (script) should you wish.

Hope this makes sense

James


James Kelly

http://www.locationsolve.com

tgazzard
146 post(s)
#29-Jul-14 08:25

Hi James,

Thanks for the reply. You are probably right that I am trying to do to much through the sql. I have several routines that have close to 1000 queries in them. And have worked out little tricks in excel to be able to change many lines relatively easily at once.

I am sure this could be reduced through looping many of these routines as they are very repetitive but my script writing is in its infancy. Also, now that I have these set up I have been reluctant to put each of these into a script that calls each query. But maybe script is the only solution to fully automate the routine.

In regards to assigning a projection to a new drawing. I wondered whether you could write a nested query or insert statement.

Something like:

insert into [drawing 4] ([Geom (I)], [column])

select AssignCoordSys(geom ([id]), coordsys("fe" as component)), [column]

from [drawing]

But this doesn't change the geometry of the drawing you inserting it into. Neither does the project function. Why is this? Is this because it is an action query?....

Cheers

Tim

tgazzard
146 post(s)
#29-Jul-14 10:55

Or something like this:

Update [Drawing 5] set [Geom (I)] = (select AssignCoordSys(geom ([id]), coordsys("fe" as component))

from [drawing 3])

jkelly


1,234 post(s)
#30-Jul-14 00:31

Tim,

From a bit of playing around with your suggestions, it looks like we can modify the geometry of an object in the table, but not the underlying projection of the drawing, which looks to be the same conclusion you have come to. For my way of thinking, this should be part of an ALTER TABLE statement, which Manifold does support, but I see no way to alter the geometry column things like coordinate system.

I'd love someone to prove me wrong, but from what I can see, the only way to alter the coordinate system of an existing drawing is to use through the object model.

Having said this, though, the only thing you would have to do via scripting (object model) is to create the drawing and modify the coordinate system. Everything else could be controlled through insert sql.

If you wanted to cheat a little, you could alter the default coordinate system that Manifold creates drawings to be what you are after. Bit of a hack, but might get you closer to what you are after.

Changing tack, if you do want to control everything via SQL, have you thought about Postgresql? It has the ability to reproject via queries and has very powerful spatial sql abilities. It's a quality database, and perhaps best of all, is free. Manifold can also link to drawings in postgres and certainly in my case, I do most of my heavy lifting spatial sql in there.

Sorry I can't be much help in what you are after

Cheers

James


James Kelly

http://www.locationsolve.com

tgazzard
146 post(s)
#30-Jul-14 23:03

Thanks James for you reply.

I didn't know that I could change the default coordinate system. I will look into how to do this.

In regards to Postgresql/Postgis. I do use this also for large datasets that we sometimes produce. I hadn't looked into the ability of Postgresql to reproject via queries though. So will also look into this.

I think in the short term I will do two things:

1) Try changing the default coordinate system.

2) See whether there are any batch scripts that are posted on the forum that can assign a projection to lots of drawings in a batch run.

Thanks for help with this.

Tim

tjhb
10,094 post(s)
#31-Jul-14 09:23

Tim,

1) Default coordinate system

I didn't know that I could change the default coordinate system.

I think you were right about that. The default is usually Orthographic, or for some purposes Latitude/Longtiude. See Adam's pointers here.

2) Changing projections by batch script

There are at least scripts to *assign* projection to many components at once. See e.g. here and here. It would be straightforward to adapt these to *change* projection for multiple components. (Can help.)

3) Changing projection by query

[I tried to be clear here, but wasn't. So I've removed these comments.]

Manifold User Community Use Agreement Copyright (C) 2007-2021 Manifold Software Limited. All rights reserved.