September 17, 2018 at 1:06 pm
Sergiy - Friday, September 14, 2018 8:59 PMGrant Fritchey - Friday, September 14, 2018 8:36 AMInsurance company I used to work for LOVED geospatial data. "Now where is that factory you want us to insure? Huh... that's a 50 year flood plain, here's the bill." Tons of uses for spatial there.
How do they know about "50 year flood plain".
50 year
flood
plain
It does not sound like geospatial data.
Wouldn't you store this as a geospatial polygon of some sort? ie., specify the plain (area) as a series of coordinates?
Seriously, even though I know jack about geo-anything on SQL, I would really doubt that Grant is making things up here.
September 17, 2018 at 1:19 pm
Jeff Moden - Sunday, September 16, 2018 9:11 AMTomThomson - Sunday, September 16, 2018 4:47 AMThat tends to depend on the langage. There is no use whatever for GOTO in anguages like Haskell, Hope, Miranda, Parlog, Prolog, and many more - indeed I have never come across any declarative language that has a GOTO statement (and I'm thoroughly fed up with hearing or seeing "T-SQL or PL-SQL or equivalent for some other RDBMS is a declarative language" when quite clearly none of them is.
SQL can often be used in non-declarative languages to eliminate a lot of code repetition and to cope with a shortage of structured flow-contol statements. But even in an essentially non-declarative language there may be no use for GOTO if the language is well designed.Heh... agreed on the "declarative" issue but people get all frothy and puffed up when I say that SQL Server boils down to essentially being a symbolic macro language that basically works with a file system like any other program might (B-Tree notwithstanding). Open or grab an existing connection to the file, find a row, read the row, do something with the row, loop until done, close the connection or let someone else use the connection. In the case of SQL Server, it finds a page and reads a row from it but that's not much different than reading from a file in DOS where a whole sector may be read from the disk. The big thing is that it has taken the pain away by making it so that we don't have to write loops anymore... it does all that behind the scenes.
SQL sure looks declarative. If it has some procedural parts to it, does that really remove all declarative parts? Also, B-Tree by definition can work on "flat files," if the file system interface has seek implemented right? And why the fallback to DOS? Doesn't
Windows have the same (if not more) read and write semantics to file systems available to the programmer?
September 17, 2018 at 4:31 pm
patrickmcginnis59 10839 - Monday, September 17, 2018 1:19 PMSQL sure looks declarative. If it has some procedural parts to it, does that really remove all declarative parts? Also, B-Tree by definition can work on "flat files," if the file system interface has seek implemented right? And why the fallback to DOS? Doesn't
Windows have the same (if not more) read and write semantics to file systems available to the programmer?
That tends to depend on what counts as SQL and what doesn't. For T-SQL, PL-SQL etcetera, there's clearly an absence of declarativeness, And in IBM's early SQL a single statement might be non-declarative, because it could see some of its own modifications as if they were original data - a rather famous problem (detected very early on and fixed, I believe, with a redefinition of the semantics of the single statements involved) whose name I have forgotten (but it's been discussed more than once in SQLServerCentral.com forums).
But for PL-SQL and/or T-SQL (and probably some other SQL-based languages) there were some attempts at arranging things so that a sequence of SQL statements would be declarative - but that could only be done for a sequence within a transaction, with isolation enforced as one of SERIALIZABLE or READ COMMTTED SNAPSHOT - all the other "isolation" levels make sequences of two or more DML or DDL statements potentially nondeclarative. An SQL-oriented person might think of declarativeness within a transaction as an extreme form of repeatable-read (with no "phantoms"), much as in Haskell declarativeness allows state change as something that appears at something rather like an SQL commit point.
Tom
September 17, 2018 at 7:48 pm
patrickmcginnis59 10839 - Monday, September 17, 2018 1:06 PMSergiy - Friday, September 14, 2018 8:59 PMGrant Fritchey - Friday, September 14, 2018 8:36 AMInsurance company I used to work for LOVED geospatial data. "Now where is that factory you want us to insure? Huh... that's a 50 year flood plain, here's the bill." Tons of uses for spatial there.
How do they know about "50 year flood plain".
50 year
flood
plain
It does not sound like geospatial data.Wouldn't you store this as a geospatial polygon of some sort? ie., specify the plain (area) as a series of coordinates?
Seriously, even though I know jack about geo-anything on SQL, I would really doubt that Grant is making things up here.
OK, let's work it out.
You have a complex polygon defining the area affected by the floods in 50 last years.
Well, you have hundreds of polygons defining all the areas affected by floods within a county/state/country, etc.
And you have another polygon defining the area occupied by the factory.
And you have several plygons for buildings, roads, warehouses, and other structures used for normal operation of the factory and having different levels of vulnerability to a flood.
Now, what kind of T-SQL query do you use to estimate which flooding polygons overlap with the factory polygon, what is the severity of a possible impact on each of the factory structures?
You don't.
You need to use specialised GIS software to read and interprete those polygons amd figure out which ones are relevant to each other.
So, what does it all have to do with a relational database?
Well, pretty much nothing.
That's what happening.
We load some data having its own internal structure into tables, inflatind DB space allocation, occupying precious CPU resources, memory, IO system, eating up into the buffer pool etc.
And then what? How can SQL Server use it?
It can only read it back and pass the data to a specialised GIS software to work it out.
Oh, yeah, that GIS software can also give SQL Server a work of indexing that bunch of data, again, using the server resourses whach obviously are not needed for any other activities.
And those indexes, again, cannot be used by SQL Server itself, it's for that side kick GIS software to use.
The question pops up again - what's the point of stuffing a relational database with geospatial data?
In the agfe of Big Data every other platform goes out of its way in attempt to spread the data, distribute it amongst multiple hardware nodes, and MS makes every effort to encourage developers to stuff a sigle-server databases with GIS, BLOB, XML, JSON and all other kind of non-relational data.
Do you still believe an introduction of geospatial data into SQL Server was a smart move?
Do you believe it was an improvent to the platform?
_____________
Code for TallyGenerator
September 18, 2018 at 6:02 am
patrickmcginnis59 10839 - Monday, September 17, 2018 1:06 PMSergiy - Friday, September 14, 2018 8:59 PMGrant Fritchey - Friday, September 14, 2018 8:36 AMInsurance company I used to work for LOVED geospatial data. "Now where is that factory you want us to insure? Huh... that's a 50 year flood plain, here's the bill." Tons of uses for spatial there.
How do they know about "50 year flood plain".
50 year
flood
plain
It does not sound like geospatial data.Wouldn't you store this as a geospatial polygon of some sort? ie., specify the plain (area) as a series of coordinates?
Seriously, even though I know jack about geo-anything on SQL, I would really doubt that Grant is making things up here.
Pretty much, yes. The maps showing flood zones (they are defined by how often and how likely, annual, 5 year, 10 year, 20 year, 50 year, 100 year, 500 year... I think that was the top, but I don't remember for certain) are loaded into a database (maps are either purchased or built in-house, my old employer did both). The location goes into the database as a parameter value (two actually of course, lat/long). We can query flood, wind, earthquake, you name it, based on location.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 18, 2018 at 6:04 am
Sergiy - Monday, September 17, 2018 7:48 PMDo you still believe an introduction of geospatial data into SQL Server was a smart move?
Do you believe it was an improvent to the platform?
Yes.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 18, 2018 at 6:33 am
Grant Fritchey - Tuesday, September 18, 2018 6:04 AMSergiy - Monday, September 17, 2018 7:48 PMDo you still believe an introduction of geospatial data into SQL Server was a smart move?
Do you believe it was an improvent to the platform?Yes.
+90N,180W
😎
I've found the SQL Server's geosptial data type and the associated features very useful, my thought is that the dislike for it comes from similar reasons as the dislike for XML, lack of knowledge and understanding.
September 18, 2018 at 6:55 am
TomThomson - Monday, September 17, 2018 4:31 PMpatrickmcginnis59 10839 - Monday, September 17, 2018 1:19 PMSQL sure looks declarative. If it has some procedural parts to it, does that really remove all declarative parts? Also, B-Tree by definition can work on "flat files," if the file system interface has seek implemented right? And why the fallback to DOS? Doesn't
Windows have the same (if not more) read and write semantics to file systems available to the programmer?That tends to depend on what counts as SQL and what doesn't. For T-SQL, PL-SQL etcetera, there's clearly an absence of declarativeness, And in IBM's early SQL a single statement might be non-declarative, because it could see some of its own modifications as if they were original data - a rather famous problem (detected very early on and fixed, I believe, with a redefinition of the semantics of the single statements involved) whose name I have forgotten (but it's been discussed more than once in SQLServerCentral.com forums).
But for PL-SQL and/or T-SQL (and probably some other SQL-based languages) there were some attempts at arranging things so that a sequence of SQL statements would be declarative - but that could only be done for a sequence within a transaction, with isolation enforced as one of SERIALIZABLE or READ COMMTTED SNAPSHOT - all the other "isolation" levels make sequences of two or more DML or DDL statements potentially nondeclarative. An SQL-oriented person might think of declarativeness within a transaction as an extreme form of repeatable-read (with no "phantoms"), much as in Haskell declarativeness allows state change as something that appears at something rather like an SQL commit point.
My thoughts are that the interesting parts of T-SQL are declarative, and in their absence T-SQL would just be a rather slow interpreted language with some database capabilities. I view the procedural elements of T-SQL and others as additions to allow turing complete programming when needed.
For the individual SQL statements that work on joined sets, we don't specify how the results are to be achieved, we just specify the results and the planner goes about determining a decent set of operations that will achieve them.
I do agree that T-SQL has some imperative parts, I just think a major interesting part of T-SQL (ie., the non procedural SQL parts) should be allowed to be called declarative. I've taken a liking to using the "declarative" description in place of "set oriented". Obviously personal preference and an offered 2 cents worth of opinion!
That tends to depend on what counts as SQL and what doesn't. For T-SQL, PL-SQL etcetera, there's clearly an absence of declarativeness, And in IBM's early SQL a single statement might be non-declarative, because it could see some of its own modifications as if they were original data - a rather famous problem (detected very early on and fixed, I believe, with a redefinition of the semantics of the single statements involved) whose name I have forgotten (but it's been discussed more than once in SQLServerCentral.com forums).
Sounds like the "halloween" problem and in my opinion it was just a lapse in implementing the SQL standard, and I think the solution was to "eager spool" the data that essentially took a picture of what the values were before any updating was applied. I remember Joe Celko talking about the spec should be interpreted as the updating should happen as if it were "all at once" (apologies in advance for any innacurate paraphrasing) and while obviously that can't happen, the programming should still be effected to allow the results to look like it had. The "Halloween" problem allowed the running query to have results that should have been confined to the "results" back into the "input" so to speak because the updated values moved then around and allowed them to reappear in the "input" whereas the standard really desired that "input" -> "operation" -> "output" not let any of the "output" reappear back into the "input".
September 18, 2018 at 7:05 am
Now, what kind of T-SQL query do you use to estimate which flooding polygons overlap with the factory polygon, what is the severity of a possible impact on each of the factory structures?
Are we saying that there are no operators within T-SQL that can do these sorts of operations? Not even something clr-ish?
Oh, yeah, that GIS software can also give SQL Server a work of indexing that bunch of data, again, using the server resourses whach obviously are not needed for any other activities.
And those indexes, again, cannot be used by SQL Server itself, it's for that side kick GIS software to use.
Are we also saying that there are no "spatial indexes" in SQL Server? Are we also saying that SQL Server can only work on one activity at a time?
You need to use specialised GIS software to read and interprete those polygons amd figure out which ones are relevant to each other.
Do we not even want to store spatial data in SQL?
Inquiring minds and all!
edit: I don't really know anything about geometric / geographic functionality, I did maybe one trivial example once for fun. I don't mind being educated, its an interesting topic!
September 18, 2018 at 7:12 am
Sergiy - Monday, September 17, 2018 7:48 PMDo you still believe an introduction of geospatial data into SQL Server was a smart move?
Do you believe it was an improvent to the platform?
Actually, one more. A small, feel good story about spatial data.
As a scout leader I was approached by the local veterans. They wanted us to help them with planting flags on Memorial Day (tradition in the US) on all veterans graves. We did it but noted, they had no idea where the veterans graves were. We walked the cemeteries with handfuls of flags, planting them where we spotted markers. But not all vets have markers, some where missed, etc.. Out of this was born an Eagle project, find all the veterans graves, record their location and everything we know about them from the headstone. My son was the first one to jump on the project. Talking it over we decided to also capture the lat/long of all the graves using GPS devices. Then we decided, rather than just write all this down (which is what the veterans asked for) we'd put it into a database, online, with web access for everyone. AND, we built a report that allowed the vets to print a picture linking bing maps to the Azure SQL Database and plotted every single grave location. They now didn't miss any and we saved them money because they only bought the number of flags they needed. Could we have done this without having a specific geospatial data type? Yeah, but geospatial data made it a lot easier (data integrity, no need for coded constraints, easy querying without data conversions, an almost literal translation straight into Bing, it was so easy).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 18, 2018 at 7:16 am
Eirikur Eiriksson - Tuesday, September 18, 2018 6:33 AMGrant Fritchey - Tuesday, September 18, 2018 6:04 AMSergiy - Monday, September 17, 2018 7:48 PMDo you still believe an introduction of geospatial data into SQL Server was a smart move?
Do you believe it was an improvent to the platform?Yes.
+90N,180W
😎I've found the SQL Server's geosptial data type and the associated features very useful, my thought is that the dislike for it comes from similar reasons as the dislike for XML, lack of knowledge and understanding.
Heh... my dislikes of XML are that it's seriously tag bloated, non-normalized, hierarchical, and terribly slow and, I believe, that demonstrates a perfect understanding. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
September 18, 2018 at 7:58 am
Grant Fritchey - Tuesday, September 18, 2018 7:12 AMSergiy - Monday, September 17, 2018 7:48 PMDo you still believe an introduction of geospatial data into SQL Server was a smart move?
Do you believe it was an improvent to the platform?Actually, one more. A small, feel good story about spatial data.
As a scout leader I was approached by the local veterans. They wanted us to help them with planting flags on Memorial Day (tradition in the US) on all veterans graves. We did it but noted, they had no idea where the veterans graves were. We walked the cemeteries with handfuls of flags, planting them where we spotted markers. But not all vets have markers, some where missed, etc.. Out of this was born an Eagle project, find all the veterans graves, record their location and everything we know about them from the headstone. My son was the first one to jump on the project. Talking it over we decided to also capture the lat/long of all the graves using GPS devices. Then we decided, rather than just write all this down (which is what the veterans asked for) we'd put it into a database, online, with web access for everyone. AND, we built a report that allowed the vets to print a picture linking bing maps to the Azure SQL Database and plotted every single grave location. They now didn't miss any and we saved them money because they only bought the number of flags they needed. Could we have done this without having a specific geospatial data type? Yeah, but geospatial data made it a lot easier (data integrity, no need for coded constraints, easy querying without data conversions, an almost literal translation straight into Bing, it was so easy).
What a great Eagle project!!
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 18, 2018 at 9:36 am
Sean Lange - Tuesday, September 18, 2018 7:58 AMGrant Fritchey - Tuesday, September 18, 2018 7:12 AMSergiy - Monday, September 17, 2018 7:48 PMDo you still believe an introduction of geospatial data into SQL Server was a smart move?
Do you believe it was an improvent to the platform?Actually, one more. A small, feel good story about spatial data.
As a scout leader I was approached by the local veterans. They wanted us to help them with planting flags on Memorial Day (tradition in the US) on all veterans graves. We did it but noted, they had no idea where the veterans graves were. We walked the cemeteries with handfuls of flags, planting them where we spotted markers. But not all vets have markers, some where missed, etc.. Out of this was born an Eagle project, find all the veterans graves, record their location and everything we know about them from the headstone. My son was the first one to jump on the project. Talking it over we decided to also capture the lat/long of all the graves using GPS devices. Then we decided, rather than just write all this down (which is what the veterans asked for) we'd put it into a database, online, with web access for everyone. AND, we built a report that allowed the vets to print a picture linking bing maps to the Azure SQL Database and plotted every single grave location. They now didn't miss any and we saved them money because they only bought the number of flags they needed. Could we have done this without having a specific geospatial data type? Yeah, but geospatial data made it a lot easier (data integrity, no need for coded constraints, easy querying without data conversions, an almost literal translation straight into Bing, it was so easy).
What a great Eagle project!!
And two of the cemeteries had never had maps, so the scouts had to make their own, identifying the different sections of the cemetery. Those were printed onto steel by a local company and mounted at the cemetery as well as the stuff we did for the vets. We did three of these for the three big cemeteries in town. It was really great.
Now, the bad news... the local vets didn't like going online. They exported the stuff to excel and they've been maintaining it there instead of in the database we created... not our fault. Still, they're happy every year.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 18, 2018 at 9:45 am
Grant Fritchey - Tuesday, September 18, 2018 9:36 AMAnd two of the cemeteries had never had maps, so the scouts had to make their own, identifying the different sections of the cemetery. Those were printed onto steel by a local company and mounted at the cemetery as well as the stuff we did for the vets. We did three of these for the three big cemeteries in town. It was really great.
Now, the bad news... the local vets didn't like going online. They exported the stuff to excel and they've been maintaining it there instead of in the database we created... not our fault. Still, they're happy every year.
I might "borrow" this project as we put flags out on the grave stones every Memorial Day. We never really know if we get flags on all the graves or not. Our local cemetery is pretty small because it is a small town and many gravestones have not been maintained in decades. That is another project in itself that I have suggested to one of our boys looking for an Eagle project. I suggested that he research how to properly restore headstones. Then do everything he can to contact the family members if any exist and get permission from the cemetery for the remainder. We have quite a few headstones that are nearly lost because they are small and not kept up. Thanks for the idea!!
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 18, 2018 at 10:17 am
I'm cruising through job activity monitors to look for something specific, when I run into (on a couple of different servers) jobs that me and another member of our team created with the title "Designed to Fail." We did it to test operators, alerts, and other functionality. We keep them around to test future functionality (such as a morning email about jobs that failed in the last 24 hours).
Now I'm wondering ... Does anyone else do this sort of thing? Or is my office an outlier?
Viewing 15 posts - 62,251 through 62,265 (of 66,815 total)
You must be logged in to reply to this topic. Login to reply