Posted by david on 4. January 2009 22:35
soooo … I’m going ahead & building a db backend for the map. I’ll have a main dataset wherein I can bank up points of interest … in the first go round, I’ll have Seattle Parks and their services. I’ll have a (model?, structure?) a few tables up front, that will be loaded with data by the page, that will mirror what the map is holding. Having the data in the db will make working with controls a hella lot easier, for selection of points on map, bringing some points to focus, etc ….
I really feel like I’m throwing in the towel on the coding side: my intent was to have the data mirror of the map dynamic without resorting to writing back & forth to the db. But, fully aware that it’s probably just my newbie skillz letting me down, it was a pain in the arse to get the controls & the map reflecting each other. Seems like the controls are designed to be database friendly, moreso than data object friendly.
& of course, once I sit down to design that out, I find some interesting articles on how to work geography data in SQL 08. Seems there is a lot of .Net stuff available to work with the new geography datatypes – that, one of these days, would make it easier to mirror the map.
Spatial coding using .NET - Spatial data in SQL Server 2008 - developerFusion - the global devel
Geography type in SQL Server - Spatial data in SQL Server 2008 - developerFusion - the global de
But I’m set on this course, for now. We already had a map working off the db back when we started this thing – & most code work is working off db's anyway … so the exercise is not entirely useless.
At first look, the solution seems simple:
a Shape Table
ShapeID_DB -- the primary key for the db
ShapeID_Map -- the shape id, assigned when shape is loaded by the map
ShapeType -- required: a shape is either a pushpin, a line, or a polygon
Lat -- required: a point on the map is defined by a lat-long object
Long -- required
Altitude -- usu null
Title -- short string
Description -- longer string, or could be html
PhotoURL -- optional
MoreInfoURL -- optional
Visible -- this should be driven by the map, not vice versa, i think
Loaded -- ditto
LayerID -- a foreign key to ShapeLayer table … a shape can only be on one layer
a ShapeLayer table
layerID_DB -- primary key
layerIndex -- determined by the map, is essentially the maps id for the layer
layerTitle -- a short string
layerDescription -- a longer string or html
layerShapeCount -- int, should be pulled from the map
layerLoaded -- boolean
layerVisible -- boolean
the first hurdle comes from the shapetype.
a pushpin is easy: it is defined by one point, a lat long.
a line or polygon shapetype is an array of points – at the map level.
I could just add another record in the shape table for each point on the map, whether pushpin or polygon. Advantage: simple. Disadvantage: violates the relational principles … the shape level data, Title, Desc, etc etc would have to be repeated for each record.
I could have an xml field in the Shapes Table that would, if we’ve got a multipoint shape, hold the array. Advantage: that I can write individual point data in with the points. Disadvantage: the individual points are not easily accessible from outside the shape. ( or are they & I just don’t know how )
I could have a third table, a master table of shapes, with a child table of points.
Advantage: all points are accessible, searchable, changeable, moveable …. Disadvantage: extra work loading data into the tables.
hour long datatype tangent:
SQL Server programming Managing Large Datasets for Reverse Geocoding -
Expert SQL Server 2005 Development chapter 9 discusses spatial data - Hugo Kornelis also authors
Use FLOAT for lat & long ( Machanic, p.256)
This guy uses Double in his C#, discussion recommends float … aah ok:
we use Float in SQL Server … but in C# LatLong we use Double … more information re accuracy of float – deep discussion
whew. use float in sql, use double in c#.
good overview of the asp.net map control: CodeProject Data Visualization with the Virtual Earth ASP.NET Controls
“Lines and Pushpins
One thing you have to be careful of when creating shapes that are lines (or polygons, but we'll come to that later on) is that by default a pushpin is also displayed to go along with the line (see right) in order to give the user something to hover over so they can gain more information. If these pushpins are not desired then the IconVisible property needs to be set to false.
shape.IconVisible = false;
On the other hand, if a pushpin is desired for each line then you can control where the pushpin appears. By default they are somewhere along the middle of the line. The IconAnchor property can be set to the point at which the pushpin is to appear. For example, to set the pushpin to appear at the start of a line use:
shape.IconAnchor = shape.Points[0];
You can also do the other customising of the pushpin as I showed you in my previous post (Using PushPins with the Virtual Earth ASP.NET control). “
To restate – because I would like all my points ( quickly, easily ) accessible from the controls – I will have a separate table for points, the points will indicate to which shape they belong.
something like that, anyway. not that altitude appears in the shapes table and points table. this is temporary … a polygon can only have one altitude. I would need to validate that all the points of a shape are of the same altitude. I’ll store altitude in with the Shape definition until something better comes up … further, I don’t really need it yet. the maps are 2D & doesn’t seem to be much to do with altititude till we get topo maps
Using Table Valued Functions in SQL Server 2005 to Implement a Spatial Data Library