September 18, 2018 at 5:53 pm
Grant Fritchey - Tuesday, September 18, 2018 6:02 AMPretty 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.
OK, I have a standalone SQL2017DEV instance here.
Can you please show me a query I have to run to see if any part of the polygon "A" with any of a flood zones?
_____________
Code for TallyGenerator
September 18, 2018 at 6:12 pm
[/quote]
Yes.
[/quote]
+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.
[/quote]
I've found that PLE on the Prod server I was digging into was on 6ms during te process of generating a report.
The process was running for about 2 hours every working day, and for those 2 hours the server was pretty much unresponsive.
I found that such behaviour was caused by the implementation of the JOIN between 2 entities.
Developers who designed the database must have been very XML savvy, so they did not bother with parsing inbond XML messages into a relational structure, saved the data in XML columns and used ").value(" to join 2 entities for the report.
When I extracted the value and saved into a normal column with an appropriate data type the duration dropped to less than 10 minutes and PLE remained almost unaffected in a process.
It was just 1 value had to be extructed from an XML column.
Must be due my lack of knowledge...
_____________
Code for TallyGenerator
September 18, 2018 at 6:17 pm
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).
I understand why you used geospatial data for mapping.
I don't understand - why Azure?
_____________
Code for TallyGenerator
September 18, 2018 at 6:28 pm
patrickmcginnis59 10839 - Tuesday, September 18, 2018 7:05 AMNow, 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!
1. What CLR has to do with T-SQL?
Do CLR modules even use optimizer or any other part of the database engine?
2. There are no spatial indexes that SQL Server engine can use.
3. I'd never do it.
Same about XML, JSON, PDF, AVI, and any kind of FILESTREAM.
_____________
Code for TallyGenerator
September 18, 2018 at 6:55 pm
Sergiy - Tuesday, September 18, 2018 6:17 PMI understand why you used geospatial data for mapping.
I don't understand - why Azure?
Azure makes complete sense. It's likely the organization using the data has no office or data center, let alone a server.
Same with the Eagle Scout. He cannot host this.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
September 18, 2018 at 7:18 pm
Michael L John - Tuesday, September 18, 2018 6:55 PMAzure makes complete sense. It's likely the organization using the data has no office or data center, let alone a server.
Same with the Eagle Scout. He cannot host this.
Azure is not only cloud database hosting option.
https://www.a2hosting.com/database-hosting
My question remains the same - why Azure?
_____________
Code for TallyGenerator
September 19, 2018 at 1:44 am
Sergiy - Tuesday, September 18, 2018 6:28 PM2. There are no spatial indexes that SQL Server engine can use.
Introduced in SQL Server 2008. You're welcome.
September 19, 2018 at 1:50 am
Sergiy - Tuesday, September 18, 2018 5:53 PMOK, I have a standalone SQL2017DEV instance here.
Can you please show me a query I have to run to see if any part of the polygon "A" with any of a flood zones?
I never worked with spatial data myself. It took me 2 minutes to find this on Google, then 3 more to work out the difference between stIntersects and stOverlaps.
You're welcome.
September 19, 2018 at 5:44 am
Sergiy - Tuesday, September 18, 2018 6:17 PMI understand why you used geospatial data for mapping.
I don't understand - why Azure?
Multiple people needed access to an application and there was no infrastructure to support said application. No servers, no hardware. I guess I could have built something that someone could put under their desk somewhere, but then multiple scouts and the public in general, wouldn't have had access. Azure was simple. It was on the internet. I know it better than I know AWS (which would be my next choice in this situation).
"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 19, 2018 at 5:46 am
Sergiy - Tuesday, September 18, 2018 7:18 PMAzure is not only cloud database hosting option.
https://www.a2hosting.com/database-hosting
My question remains the same - why Azure?
Did they have database as a service hosting six years ago when we built this? Probably not. Azure did. Why the odd resistance to Azure?
"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 19, 2018 at 7:38 am
Grant Fritchey - Wednesday, September 19, 2018 5:46 AMSergiy - Tuesday, September 18, 2018 7:18 PMAzure is not only cloud database hosting option.
https://www.a2hosting.com/database-hosting
My question remains the same - why Azure?Did they have database as a service hosting six years ago when we built this? Probably not. Azure did. Why the odd resistance to Azure?
My question too.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
September 19, 2018 at 8:29 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 have to agree - this is an awesome Eagle Scout project.
Since they exported the data to Excel and are using that... is the Azure instance still up and running? If so, care to supply a link where we can look at it?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 19, 2018 at 9:02 am
Michael L John - Wednesday, September 19, 2018 7:38 AMMy question too.
He hates the hand that feeds him? :unsure:
September 19, 2018 at 9:17 am
WayneS - Wednesday, September 19, 2018 8:29 AMI have to agree - this is an awesome Eagle Scout project.
Since they exported the data to Excel and are using that... is the Azure instance still up and running? If so, care to supply a link where we can look at it?
Looks like they may have shut it down... FUDGE!
"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 19, 2018 at 9:19 am
Grant Fritchey - Wednesday, September 19, 2018 9:17 AMWayneS - Wednesday, September 19, 2018 8:29 AMI have to agree - this is an awesome Eagle Scout project.
Since they exported the data to Excel and are using that... is the Azure instance still up and running? If so, care to supply a link where we can look at it?Looks like they may have shut it down... FUDGE!
Flooded or flushed?
😎
Viewing 15 posts - 62,266 through 62,280 (of 66,815 total)
You must be logged in to reply to this topic. Login to reply