like stepping through a door

like stepping through a door
or falling down a stair
from this to the next
can happen anywhere

anytime it can happen
and one day it will
a slip or a stumble
a fall or a spill

be careful

p

MS SQL Server Spatial to Google Maps KML

I don't usually post code tips on my blog but this one might help some other poor soul.

You have some map data stored in SQL Server as geometry (or geography) and you want to extract it in a format that you can use in your google maps mashups.

I wanted a Q and D solution that would be confined entirely to SQL Server functions. I can certainly write C type code (maybe a million lines in my life) but i wanted this totally within SQL Server and easily used.

Note that my maps are relatively simple - there are no interior rings though there are all sorts of combinations of MULTIPOLYGONs, GEOMETRYCOLLECTIONs and simple POLYGONs. You should check your own maps for complexity and if necessary add support for interior rings (which i don't need).

Here's the 3 main pieces of code i wrote to satisfy my needs.

---

This first function returns the MultiGeometry part of my Placemark (sorry, everything above you will have to sort out yourself):


CREATE FUNCTION [dbo].[kmlGeometryElementsExtract]
(
@p_Spatial geometry
)
RETURNS xml
AS
BEGIN

DECLARE @v_spatialXML xml

SET @v_spatialXML = dbo.kmlGeometryElementsExtractAux(@p_Spatial.ToString())

DECLARE @v_collection xml
DECLARE @v_multi xml
DECLARE @v_poly xml

SELECT
@v_multi = nref.query('.')
from @v_spatialXML.nodes('/bracket') as R(nref)
where
nref.value('.[1]/@tag','varchar(100)') = 'MULTI'

IF @v_multi IS NOT NULL
BEGIN

RETURN (

SELECT
(
SELECT
dbo.kmlGeometryCoordsConvert(nref.value('.','VARCHAR(MAX)')) as 'outerBoundaryIs/LinearRing/coordinates'
from @v_multi.nodes('//bracket') as R(nref)
where
nref.value('./bracket[1]','VARCHAR(MAX)') IS NULL -- no backet below
FOR XML PATH('Polygon'), TYPE
)
FOR XML PATH('MultiGeometry'), TYPE
)

END

SELECT
@v_collection = nref.query('.')
from @v_spatialXML.nodes('/bracket') as R(nref)
where
nref.value('.[1]/@tag','varchar(100)') = 'COLLECTION'

IF @v_collection IS NOT NULL
BEGIN
RETURN (

SELECT
(
SELECT
dbo.kmlGeometryCoordsConvert(nref.value('.','VARCHAR(MAX)')) as 'outerBoundaryIs/LinearRing/coordinates'
from @v_collection.nodes('*/bracket') as R(nref)
where
nref.value('.[1]/@tag','varchar(100)') = 'POLYGON'
FOR XML PATH('Polygon'), TYPE
)
FOR XML PATH('MultiGeometry'), TYPE

)
END

SELECT
@v_poly = nref.query('.')
from @v_spatialXML.nodes('/bracket') as R(nref)
where
nref.value('.[1]/@tag','varchar(100)') = 'POLYGON'

IF @v_poly IS NOT NULL
BEGIN
RETURN (
SELECT
(
SELECT
dbo.kmlGeometryCoordsConvert(nref.value('.','VARCHAR(MAX)')) as 'outerBoundaryIs/LinearRing/coordinates'
from @v_poly.nodes('*/bracket') as R(nref)
where
nref.value('.','VARCHAR(MAX)') IS NOT NULL
FOR XML PATH('Polygon'), TYPE
)
FOR XML PATH('MultiGeometry'), TYPE
)
END

RETURN NULL
        
END

---

The kmlGeometryElementsExtract function above calls this one - which looks like a hack but it turns out to be the fastest way to skin this cat - i had written a FSM to do the job but SQL Server string handling is way too slow if you are concatenating one char at a time to a large string.

 CREATE FUNCTION [dbo].[kmlGeometryElementsExtractAux]
(
@p_SpatialString VARCHAR(MAX)
)
RETURNS varchar(max) -- in xml format
AS
BEGIN

DECLARE @v_result VARCHAR(MAX)

SET @v_result = REPLACE(@p_SpatialString,'GEOMETRYCOLLECTION (','<bracket tag="COLLECTION">')
SET @v_result = REPLACE(@v_result,'MULTIPOLYGON (','<bracket tag="MULTI">')

SET @v_result = REPLACE(@v_result,', POLYGON','POLYGON')
SET @v_result = REPLACE(@v_result,'POLYGON (','<bracket tag="POLYGON">')
SET @v_result = REPLACE(@v_result,', LINESTRING','LINESTRING')
SET @v_result = REPLACE(@v_result,'LINESTRING (','<bracket tag="LINESTRING">')

SET @v_result = REPLACE(@v_result,'), (',')(')

SET @v_result = REPLACE(@v_result,'(','<bracket>')
SET @v_result = REPLACE(@v_result,')','</bracket>')

RETURN @v_result

        
END

---

The kmlGeometryElementsExtract function above also calls this one - which simply formats the coordinates element contents to suit my needs - YMMV:


CREATE FUNCTION [dbo].[kmlGeometryCoordsConvert]
(
@p_CoordsString VARCHAR(MAX)
)
RETURNS varchar(max)
AS
BEGIN

/* this line replaced with line from post comment below   
RETURN CHAR(10) + REPLACE(REPLACE(@p_CoordsString,', ',',0' + CHAR(10)),' ',',') + ',0' + CHAR(10)

here's the new line:
*/

 RETURN CHAR(10) + REPLACE(REPLACE(REPLACE(@p_CoordsString,', ',',0' + CHAR(10)),' ',',') + ',0' + CHAR(10), CHAR(10), CHAR(10) + ' ')

END

---

Hope you find that useful

pop


ps see DB rankings here: DB-ENGINES

Chris Hedges: Brace Yourself, the American Empire Is Over

long but well worth watching

 

Dear Apple, Dear Samsung

I am still using an old Nokia 1110i Phone:

Work gave me a nice new HTC smart phone some time back but after i used it for a week i threw it in my drawer and went back to the Nokia.

My family asks why i don't have a smart phone - they all have one.

I don't have a smart phone for the following reasons:

• The battery lasts for little more than a day and if i forget to charge it it becomes a useless brick
• Email is not meant to be answered instantly - why should i want it everywhere i go?
• There's not a single smart phone that provides what i want from a hand-held computer
• At work and at home i have great huge screened desktops running Linux and Windows - in between i'm driving or riding my bike
• I live in a city where i grew up and don't need GPS
• I don't play games
• I don't like to spend money unnecessarily

I would buy myself (or get work to buy me) a nice new smart phone if it could provide what i want:

• at least 2 days of battery life even under moderate use
• connectivity so that i can use a normal large desktop screen, keyboard and pointer - ie i want to plonk it down on the desk, have it recognise available interface devices and automatically start to use them
• ideally the whole phone is a solar energy collector that augments the battery life

If you mighty technical geniuses can give me a nice unix based pocket sized computer with built in coms for everything i need - i'll spend up to $2000 for it

In the mean time i'm sticking to my Nokia - it has a battery life of up to 15 days, does not pester me with emails, costs me nothing if i lose it, does not break if i drop it, and most of all

does not communicate to the world that i'm a brainless consumer

pop

ps, if such phones were available how long do you think Microsoft would last - everyone on earth could get rid of their desktops forever and most of their laptops and their stupid tablets (a touch sensitive screen interface need only be that - an interface)

of course, maybe what Microsoft is planning is to beat the competition by being the first with such a device

but i doubt it - they are still stuck on blood-sucking the dying life out of their poor trapped MS Office users

brainless leeches

p

Contracting for a Fall

Patricia Kuhl: The linguistic genius of babies (TED)

This talk is just fascinating

it suggests that if you really want your kids to be awesome then you should expose them to as many native foreign language speakers as possible for as much as possible and starting as soon as they are born.




and this too

p

TED:Money can't buy Happiness

p

Good buy? Windows?

oh Microsoft you gave me
all the worst years of my life
all the crashes and the blue screens
suffered through so many nights
I was so buzy in the backrooms
writing work-arounds for bugs
in your costly new releases
foisted on us guileless mugs

and i'd been always
one forced to buy you


I've been using and working with computers since 1978. Back then i caught the bug about the time i started university. I recall walking through the University of New South Wales wondering how i was going to learn about computers. There was no computer science course. There were only papers that one could take in math, science and engineering that included some computer programming. I enrolled in a course in surveying because that seemed to offer me the most options to take papers that included computing. I learned FORTRAN, APL, Pascal and how to program HP programmable calculators. The FORTRAN programming was done on punch cards.

I started a computer science degree in 1983. That then gave me a bunch of majors. I was right into DCL and Unix. I was an avid owner of any computer i could afford. I learned every language i could get my hands on an interpreter or compiler for. I had the very first copy of K & R in the country.

I remember when DOS and Windows came along and most particularly when MS Word came along and gave us WYSIWYG. Gave? No way - MS always cost big big bucks. But the thing is, even though there were way better things around (Tex, Unix, DCL) the cost of a PC and the rush by "business" people to computerize saw MS take off and what i have always thought was true rubbish rose to dominate the desktop market. Oh yes there came NT but even NT was a pale shadow of the power of Unix and C.

Oh woe is me how gawd awful it has been to watch. I have stuck with *nix and time to time have been pure Solaris or Linux but work has always been easier to get on Windows no matter just how bad it has been.

Sure Windows has had some highlights though all of them very costly. But mostly it has become the same as it was when IBM was on top - gutless, uneducated and talentless people have dominated the business market for a long time and they have been stuck on buying Windows because they think they can understand it. Idiots.

The overly long dominance of Microsoft is now drawing to a close. They will retain some niches for some time i am sure (eg SQL Server) and even produce some good stuff (who knows - though Nokia is staring down the barrel of having made the wrong bet there). The writing is on the wall. Linux and other Unix variants will dominate.

Just recently i have completely abandoned Microsoft at home. My home network is now all Linux Fedora 14 and 16. I'll never go back to Microsoft. Not when i can have the sort of power i have here at the price i pay for it all: $0.

The internet runs on Linux. The world is rushing to adopt Android which is Linux. The Apple devices all run a version of Linux. Routers and switches, embedded devices in cars and fridges and almost everything else electronic are Linux. Many of the emerging weapons technologies (like hive-minded swarms of thopters and other RS and WD platforms) use variants of Linux.

Good bye Windows and good riddance.

Nervous Structure (Field) (2012)

This is pretty cool.


Nervous Structure (field) (2012) from Cristobal Mendoza on Vimeo.

Thanks to Neuro Times for the link

p

Nuclear and Coal Swan Song Video

This is pretty, well, um, er, funny?




p