Data Modelling for mapping locations.. 1

[Tokhra]

Hi all,

Im in the process of modelling a database, a property database to be exact, so this is going to need a good locational mapping system, the first thing to come to mind is the following:

Country
---------------------------------------
CountryID | Name

Region
---------------------------------------
RegionID | CountryID | Name

Town
---------------------------------------
TownID | RegionID | (CountryID) | Name

But I wanted it to be a little more flexible then that, so I thought about

Location
----------------------------------------
LocationID | ParentID | Name

Now, this one works, as I can go in as many levels as I want e.g.

Country, Region, Town, And so on

However, if I wanted to query every property in Town, it would get quite complicated, because each property would have a LocationID, meaning id have to SELECT * FROM Property WHERE LocationID = x, which wouldn't give me the properties in the lower level locations of Town.

So, I thought of having the ID like this:

1,14,205,40

This represents the Id's as they go down the level (country, region, town, and so on)

Now, to get all the properties in town I could SELECT * FROM Property WHERE LocationID LIKE '1,14,205%' Which is fine

However, I also have a requirement (when drilling down) to be able to select only the next level locations, so if I have country and I want to see all the next level (regions) how would I query that?

Sorry if this is just a big mess of confusion, i've been trying to figure it out for a while, if you have any better ways to implement a flexible location mapping system please let me know!

Thanks,
Matt.

 

[r937]

since you can have the same town in different regions ...

(how many towns called toronto are there? i mean, besides the one in canada? there are 3 that i know of in the u s of a)

... therefore the simplest method would be to use country, region, town as varchars

otherwise you are looking at not a hierarchy of codes, but a many-to-many, probably at each level

rudy

http://r937.com/

SQL Consulting

 

[dbomrrsm]

a lot depends on the size of the DB and what it is going to be used for.

Some may say put it all in one big table some will say split it down over that you suggested in your first example above.

What volumes of data - data types - and usage are you expecting - from that a better idea of how to approach it can be made.

[blue]DBomrrsm[/blue]

 

[Tokhra]

Thanks for the responses,

Im looking to map out Mainland Spain, the Balearic islands and the Canary Islands, including all of their regions, towns and sub areas etc.

Basically I want it to be as efficient and functional as possible, if plans go well im expecting it to be quite a busy site.

Im swaying towards the Location table with the id hierachy at the moment, e.g. 1,1,20,40 because that allows it to be very flexible, and also allows me to easily search and include all properties in sub locations, e.g.

SELECT * FROM Property WHERE LocationID LIKE '1,1%'

The only problem I see with this is that I cannot figure out how to query it and only include the next level of locations, not all sub's.

Regarding rudy's point about the locations having multiple names, im not sure I fully understand, each location will have its own ID, so if theres two places called toronto, the id's would be different because of the hierachy e.g.

1,30,1 toronto
1,20,9 toronto

The preceding numbers would represent the differing higher level locations (usually country and region).

Thanks for the help so far,
Matt.

 

[r937]

and the query for finding all torontos is ...?

your comma-separated list of keys makes for clumsy sql

rudy

http://r937.com/

SQL Consulting

 

[Tokhra]

Sorry, I probably should have mentioned this, when a user is using the site they will drill down through the values, so by the time im querying all properties in 'toronto' i'll be in a situation where I know the ID.

What else do you suggest that gives the same level of flexibility? Im open to suggesstions thats why i've posted here to discuss with some sql guru's

 

[r937]

okay a user drills down

is that the only way to use your site/application?

what if i show up and want to look for properties in a town called toronto?

you gonna make me drill down, or can i simply enter a search term?

how the heck am i supposed to know which province it's in?

because if i can't just search, i'm going to a different site where i can



rudy

http://r937.com/

SQL Consulting

 

[Tokhra]

OK, I could search the Location table for 'toronto', then ask you to pick e.g.

Verify Location:

Region1, Toronto
Region2, Toronto
Region3, Sub Region 1, Toronto

Select one, and i've got my id Quite a clean result on the frontend id say, although it may be a little hassle actually getting the parent names.

 

[r937]

yes, that's my point

you can search which table for toronto?

and having found it, how do you go up its hierarchy to find the top level?

think about the sql...

rudy

http://r937.com/

SQL Consulting

 

[Tokhra]

Well this is what im asking..That is the best solution that i've come up with but I realise it has its down points.

I stated that id search the 'Location' table.

I could chop off the last point on the id, and thats the id for the parent location..

These are my requirements:

- To have a extensible/flexible location hierachy. Not locked to just Country / Region / Town.

- To link property table rows to this location hierachy

- To be able to traverse the hierachy easily backward

- To be able to select rows for a specific location

- To be able to select rows for a specific location and any of its sub locations

With a bit of manipulation on the ID's from the calling code, I can achieve that with my current solution, I know it has its downfalls, but thats the best I can come up with that meets all the requirements.

Like I said, im open to suggestions.

Thanks,
Matt.

 

[vongrunt]

There are several ways to make hierarchy in SQL. All require redundant fields and some pre-calculations. My favorite is nested-set model (NSM). It is nicely described in Joe Celko's book, though changes in hierarchy are tricky to implement because proper functioning of NSM relies on enumeration of tree nodes. But because geo stuff (country/region/blah) rarely changes, this may be the way to go.

Some useful links here: faq183-5322.

Another alternative is very similar to you original thought: complete path stored in varchar column, but use fixed number of bytes for each level. This will make frequent queries a bit easier:

Gimme next level:
WHERE locationPath LIKE '0001000140205%' AND LEN(locationPath) = 16

First two levels from root only:
WHERE LEN(locationPath) = 8

Traverse hierarchy backward:
SELECT A.blah blah
from myTable A
WHERE A.locationPath + '%' LIKE '0001000140205%'
ORDER BY LEN(A.locationPath)

Etc etc. Note that locationPath serves only for hierarchy stuff (redundant column), not referential integrity.

 

[Tokhra]

Vongrunt, thanks for the links - im getting somewhere now.

Is there anyway to make the primary key on the Location row be a 4 digit number? or, how can you pad out a number to be 4 digits for when im working with it.

Thanks,
Matt.

 

[vongrunt]

Lemme elaborate. Here is general table structure:

LocationID int primary key (identity?)
ParentLocationID int -- can be NULL
Name
LocationType -- country, region, city, blah, possible foreign key
LocationPath varchar(long_enough)

Columns LocationID/ParentLoc do referential integrity (standard adjacency list model). Column LocationPath contains complete path. Each parent node is represented as 4-digit string so:

1 NULL Country1 0001
2 1    Region1  00010002
3 1    Region2  00010003
4 2    City1    000100020004

This is equivalent to:

Country1
	Region1
			City1
	Region2

Each time a new location is inserted, calculate it's LocationPath (from LocationID and parent LocationPath). Note again that LocationPath is completely redundant; even if you mess up something hierarchy can be reconstructed from adjacency list columns.

Padding is simple:

declare @num int; set @num = 23
select right('0000' + cast(@num as varchar), 4)

Also consider that 4 digits may not be enough (up to 9999 locations).

 

[Tokhra]

Great, thanks for the breakdown.