Hello all

I hope someone can help me get a solution to the challenge described below. I have not ben able yet to figure out a way to solve it using the available Manifold tools. The file ExternalNumbers.map has been uploaded for reference.

I have a drawing representing blocks in a city. One of the attributes on the drawing table are the house numbers. There is a column named NumExt which has all the external numbers belonging to that part of the block, separated by commas as follows:

3,5,7,9,11,13,15,17,19,21,23,25,29,31,33,3,5.7,9,17

Obviously, different blocks have different sets of numbers as can be seen in the attached file.

I need to create a point feature for each of the house numbers contained in this column evenly aligned along the line representing the block where the numbers belong to. So there shall be one point feature for house number 3, another for house number 5 and so forth.

Does anyone have an idea on how this could be done other than manually?

I would very much appreciate any help.

Alberto

Attachments:
ExternalNumbers.map

I don`t know, but maybe you could use the technic of geocoding for your problem and this could be

an alternative for organising your datas:

Look at Manifold help: Geocoding Data Extensions

Geocoding Data Extensions

Suppose we would like to create a geocoding data extension table for ranges of addresses. We could create a table called Geocoding Data and make sure that it contains the following fields and field types:

• State - Text (ANSI, variable-length)

• City - Text (ANSI, variable-length)

• Street - Text (ANSI, variable-length)

• From Number - Integer (32-bit)

• To Number - Integer (32-bit)

• From Latitude - Latitude

• From Longitude - Longitude

• To Latitude - Latitude

• To Longitude - Longitude

In the above table each record represents a street segment that extends from a From latitude and longitude location to a To latitude and longitude location. The street segment contains addresses numbered from the From number to a To number. For example, a street segment may contain addresses from 100 to 200 on a Street called Western Avenue. An address located at 150 Western Avenue will be located at the middle of the straight line drawn from the From latitude and longitude location to the To latitude and longitude location.

The From Number used in a street segment may be greater than the To Number. More than one record can overlap for the same Street name with From Number and To Number address range so long as the overlapping number ranges are all odd or all even. The geocoding engine can exploit an all odd or all even number range to assign an odd or even street address number to the correct segment.

Consider a situation where a street segment has a From Number of 10 and a To Number of 31. This is a typical situation where a building number between 10 and 31 with that same street name will be geocoded within that segment.

If desired, the same street can be represented by more than one street segment where the ranges overlap numerically but the segments are distinguished by having all odd or all even number ranges. In the above illustration the same portion of the street is represented using three segments. There are two segments that use all even numbers for the From Number and To Number values (10 to 20 on one segment and 24 to 3011 and 31) for the From Number and To Number values. on the other segment). There is one segment that uses all odd numbers (

I have looked at the geocoding approach but the problem I have is that the house numbers come in a single column with the data separated by commas. So it is a text field that does not meet the geocoding table specifications.

So the first step should be able to split the numbers within the NumsExt column into separate individual objects. Then it will be possible to deal with each independently.

As I was thinking of a solution last night, it occured to me that maybe it will not be possible to do it using a combination of standard tools within Manifold, so some programming/scripting may be needed.

The conceptual idea would be to:

1. Create one line object with each of the house numbers opverlapping the original line

2. Obtain the centroid for each ovelapping line. This would create one point object with each of the house numbers for a given line segment.

3. Align the centroids evenly along the original line extent.

In order to perform step 1, a script should be developed to read the NumsExt column for a given field. It would identify each house number by using the commas as a separator. Once a full number is detected, a new line object would be created assigning the identified number to its NumsExt column and overlapping the original line. Do the same for the second number in the original NumsExt data for that field and create a line object for it. Continue until the last number in the NumsExt for that field is processed. Then move on to the next field and repeat the same process.

Once all the fields have been processed, the centroids can be obtained. This could be done with the standard centroids tool within Manifold.

Then, another script shall be developed to align the centroids along the original line.

I am familiar with VB so that is what I would like to use, though I have never done programming in Manifold, so any help on how to achieve this will be greatly appreciated.

Alberto

Hi Anajera

why don't you try to merge the columns together by creating an active column and do something like:

Function PLZ6

PLZ6 = Record.Data("PLZ")& Record.Data("CC")

End Function

then go to geocoding again..

Hi sorry about the above post, but here is an other sollution:

put the file in excel and format the commaseparatet numbers with the menu: DATA, Text in columns

so you have for each number a column.

then create a new columsn in excel (or in Manifold and join th estreet with the numer together:

in excel: =$E2&" "&F2

afterwards you just have to copy the new colums and add them at the bottom of your table.

hope this helps.. attached the talbe as excel with the formula in yellow

beware tthat there are some * in the column of the numers

Attachments:
sample.xls

Looking at the supplied map a script looks like it would be a real bear to me.

Here are some steps manually create points, not sure if you could get a scrip to do this...

0 normailze the map so that the majority of addr lines only have one segment

0 select a line

0 use transform Selection, Segments, # of address on the line

0 use transform Selection, Points (line's attribuites will transfer to each point)

0 delete the extra points on each end of the line

0 edit each point to leave behind only the proper address #

Most likely you will want to edit the points so that the resulting points are on the correct end of the line.

Note that in a few cases the address list ends with ... - guess they ran out of room.

This procedure would be difficult to script. You could build the points and then export the table to do the editing outside of Manifold to spread around the load. Make sure to export the lat/long or XY so you can plot the points when the editing is done. Not sure how you would know the direction to use for address progression if you export. Perhaps just edit one useful point to get the process started.

Watch out for curved lines as they will have an abundnce of points after the Segments step = perhaps draw a straight line to replace or use a smaller number of segments to make clean up easier.

Thanks for the tip. The problem I see with your suggestion is that it involves a lot of manual work which is what I like to avoid. The sample uploaded is just a fraction of the actual drawing, so performing this manually would be overwhelming.

I will post this in the Scripting section of the forum and see if an idea comes from there.

Thanks for all the ideas.

It's possible to do this pretty neatly in SQL. I've been keeping out of the discussion because I haven't got time to write the code now. When do you need it by?

Hi,

Thanks for the reply. There is no a big rush. I can wait for a few days.

Alberto

TJHB,

Have you been able to give this a look?

Thanks

Tomorrow is Sunday here. I will write something then.

Hello TJHB

Can you provide me with some guidelines on how this could be scripted with SQL? . Besides accepting the help you offered, I would like to learn as well.

Thank you

Hello TJHB

Have you had the time to give this a look? Our company shall make a decision on whether to purchase this information or not, based on the possibility to make this transformation. I would appreciate if you can confirm that it is possible to be done wth SQL.

Thank you

I'm really sorry for not having got back to you Alberto.

I have had a couple more good looks at the problem.

Yes, it can certainly be done with SQL (scripted SQL). Writing and testing the code would take 3-4 hours. I still haven't managed to find a space to do that. A good start would be t list what I think are the necessary steps.

Here's a very naive attempt in R that assumes each line object is a single straight line segment and that there is a simple relation between token order and position along the line. If it's useful I could follow it up with a better method that uses seq() between intermediate coordinates based on token number.

#R

library(rgdal)

## read as shapefile

d <- readOGR("ExternalNumbers.shp", "ExternalNumbers")

## use a list to append each result (we should pre-parse NumExt to find out the total number of points)

res <- list(ID = integer(0), NumExt = character(0), x = numeric(0), y = numeric(0))

d$NumExt <-as.character(d$NumExt)

## loop over each line segment (assuming single branches)

for (i in 1:length(d$NumExt)) {

 ## split the tokens

 blknums <- unlist(strsplit(d$NumExt[i], ","))

 ## obtain the coordinates

 coords <- d@lines[[i]]@Lines[[1]]@coords

 ## generate intermediates (assuming straight line segment)

 nn <- length(blknums)

 xx <- seq(min(coords[,1]), max(coords[,1]), length = nn)

 yy <- seq(min(coords[,2]), max(coords[,2]), length = nn)

 ## append to result

       res$ID <- c(res$ID, rep(d$ID2[i], nn))

 res$NumExt <- c(res$NumExt, blknums)

 res$x <- c(res$x, xx)

 res$y <- c(res$y, yy)

}

## turn into a SpatialPointsDataFrame and write to shapefile (attached)

res <- as.data.frame(res)

coordinates(res) <- ~x+y

writeOGR(res, "ExternalNumbersPoints.shp", "ExternalNumbersPoints", "ESRI Shapefile")

Attachments:
ExternalNumbersPoints.zip

cuda shuda wuda

Thank you MDSummer,

The results you get are exactly what I am looking for. With just a few adjustments all should be as desired. I imported the SHP files you created into Manifold and added them to the map in order to see how they overlay. The file is attached. I noticed 2 situations that shall be addressed:

1. For some reason the numbers in lines going in a general direction of NorthEast to SouthWest do not get aligned with the line. Instead they get a SouthEast to NorthWest direction.

2. The first and last house number on each segment is plotted at the very beginning/end of a line. This is causing that house numbers of two different block sections overlap in the corners. Could it be possible to modify the code so that the points at the beginning and end are not located exactly on the corners? That is to add some space from the start of the line to where the first point is actually plotted.

Besides the above, I noticed this was not done inside Manifold. I am not familiar with R and had not heard of it before. Though I was able to get an idea of what the code is doing, Can you tell me more about it? Does it need a special developing environment? Where can I learn more?

Could a similar code be written to be run directly in Manifold with VBscript for example?

Again, thank you very much for your help.

Alberto

Hi,

Here is the file. There were some problems loading it due to the size. This is a smaller version of the original one but exemplifies what I explained in the previous post.

Attachments:
ExternalNumbersPt.map

This is very ad hoc, and untested, and won't help with the NW/NE thing:

#R 

#R

library(rgdal)

## read as shapefile

d <- readOGR("ExternalNumbers.shp", "ExternalNumbers")

## use a list to append each result (we should pre-parse NumExt to find out the total number of points)

res <- list(ID = integer(0), NumExt = character(0), x = numeric(0), y = numeric(0))

d$NumExt <-as.character(d$NumExt)

## loop over each line segment (assuming single branches)

for (i in 1:length(d$NumExt)) {

 ## split the tokens

 blknums <- unlist(strsplit(d$NumExt[i], ","))

 ## obtain the coordinates

 coords <- d@lines[[i]]@Lines[[1]]@coords

 ## generate intermediates (assuming straight line segment)

 ## (generate nn + 2 and remove the first and last)

 nn <- length(blknums)

 xx <- seq(min(coords[,1]), max(coords[,1]), length = nn + 2)[-c(1, nn+2)]

 yy <- seq(min(coords[,2]), max(coords[,2]), length = nn + 2)[-c(1, nn+2)]

 ## append to result

       res$ID <- c(res$ID, rep(d$ID2[i], nn))

 res$NumExt <- c(res$NumExt, blknums)

 res$x <- c(res$x, xx)

 res$y <- c(res$y, yy)

}

## turn into a SpatialPointsDataFrame and write to shapefile (attached)

res <- as.data.frame(res)

coordinates(res) <- ~x+y

writeOGR(res, "ExternalNumbersPoints.shp", "ExternalNumbersPoints", "ESRI Shapefile")

cuda shuda wuda

I hope it's not out of place for me to say thanks here Mike. I wish I'd followed your advice and learnt R a year or two ago. The SQL I have in mind has a lot of similarities, but coding it is more laborious. Your R does ±what I would try to do by scripting a succession of queries, but much more economically. Thanks.

Mike

How shall I run this script. I know R does not run within Manifold, so can you explain me more about it?

Thanks

Sorry anajera, my post was pretty out of context but, yes R is totally separate from Manifold - if I have any excuse for posting solutions with it it is only because there is some parallel interest from other users and possible future efforts for connecting the two environments (mostly imaginary so far but compelling nonetheless).

R is an interactive programming environment, especially (and originally) good for statistical methods and graphics.

The home of all R stuff is here: http://www.r-project.org/

Download and install for Windows from CRAN: http://cran.r-project.org/bin/windows/base/R-2.7.2-win32.exe

Start R, type in the following and you can run the code above by pasting it into the console (and taking care of your working directory):

#R 

install.packages("rgdal")

BTW, the "[[1]]" and "@" stuff is exposing the internals of high level class/object structure that ought to be handled by method interfaces - R shouldn't normally be publicized that way, but when you know what's going on it's very powerful since you can use the internals in your own custom data structures and then hide them for public use using the same syntax that most of R uses. SQL syntax such as AllCoords() is similarish. Sorry again, this is not a particularly helpful "Manifold post".

cuda shuda wuda

I am downloading and installing. I also got the R manuals from the R project. Are there any guidlines available on how to use the Gdal package? I guess the GIS specific commands are documented somewhere else. Can you direct me to them?

For example, I have not yet been able to find the "readOGR" or "writeOGR" commands in the documentation I got so far .

Once you've installed an extension package (such as "rgdal") you need to load it from the library of installed packages:

install.packages("rgdal") ## works via internet connection

library(rgdal) ## load it (it won't load by default)

?readOGR ## same as help(readOGR)

?writeOGR

vignette("sp") ## information about the spatial classes

cuda shuda wuda

I understand an appreciate your help. The code that Mike provided is very helpful and I will follow it and try to learn this R scripting language. But I am also very interested in doing it with SQL or maybe VBscript in Manifold. The sample provided is just a very small section of the full data we are looking to process. In total it has data for over 4000 cities.

So, if you could help doing it with SQL in Manifold as well, I would appreciate it

Alberto