topic Re: Selection in Data Management Questions

Selection

LisaJermann — Sat, 22 Nov 2014 14:56:38 GMT

Hello everybody!

I have a huge database with sightings from whales and dolphins.

Now I want to sort them and want to make it easy for people to select in this content between e.g. the different whale types and the different years and so on. So I was thinikng about that their is for each year a single layer. But that should be something like a query. Because if their is a "new year" for example 2015 than I have no layer for this year. Thats the problem if i would do it manually.

I have no idea how to sort this problem.

Have you any Idea how to make it in ArcGIS?

Would be very thankful for any suggestions.

Re: Selection

JorgeOrellana — Mon, 24 Nov 2014 16:04:20 GMT

The database you mention, is it a file geodatabase? SQL? Access?

I am not a database expert myself but what I have seen our database people use in the past is creating views in SQL that break down the data into smaller more manageable pieces. The database admin would have control of which views to create, what data to show, how to break it down, etc. Then the users would be able to connect to the pre-filtered SQL view in ArcGIS and not have to deal with the large database but at the same time allows you to maintain the entire database as one. Another advantage of this is, if your original database tables do change, the views will reflect those changes as well, and you don't have to re-export or have multiple copies that need to be removed from MXDs or other services that use that data.

Re: Selection

LisaJermann — Tue, 25 Nov 2014 12:24:52 GMT

Thank you for your quick response.

I thought of this before but the problem I thought is that if I have for example new sightings for a new year I have to create a new layer with the new query. It would be cool if there are new sightings for exampe a new year their would be automatically a new layer or view for the new year. And not that I have to create the querys for the next ten years.

I hope you understand what I mean. But of course I don't know if that is the only way to solve the problem.

Re: Selection

LisaJermann — Tue, 25 Nov 2014 12:31:26 GMT

Thank you very much for your quick response

So the database is "saved" as an excel sheet. Thats because the people that work for that association need excel for their statistical research. If it would be easier to store it in a different kind of database it would be no problem.

I don't know if I understand you correctly. It means that I would store the data in SQL and would create views e.g. for each year or type of whale. Than I would connect the SQL database with ArcGIS and their the before created views would be automatically in the Map ?

Re: Selection

LisaJermann — Tue, 25 Nov 2014 12:33:35 GMT

Maybe I should have added that the database is till know saved as an excel sheet. If it would be easier to sort out my problem in a different kind of database its ok but of course it would be cool if it would work with that excel sheet.

Re: Selection

CattyannCampbell — Tue, 25 Nov 2014 14:17:19 GMT

It seems to me that if you don't want to go the SQL view route and want to use your excel tables that a Python script or model that you push on a yearly to create the layers could work for you. I'm not offering up such a script I wish I had the time but I'm sure someone out there has done something like this before. BTW what version of excel are you working with?

Re: Selection

JorgeOrellana — Tue, 25 Nov 2014 14:22:33 GMT

I would suggest taking it out of excel. Excel and ArcGIS have a lot of limitations and issues, such as if two people are trying to view the spreadsheet at the same time, only the person that accessed it first will be able to use it, the other would have to wait, in SQL this would not be an issue, multiple users can connect and view the data.

To clarify the view, it is somewhat like a table, there are fundamental differences between a view and a table and I'm sure some database guru might cringe at the way I am explaining it but when you connect to the SQL database that contains a view, it will show up as a "table" that you would be able to add to ArcGIS and perform whatever analysis that is needed.

Jorge

Re: Selection

LisaJermann — Tue, 25 Nov 2014 14:59:34 GMT

That sounds really good and is propably one of the best solutions. unfortunately i have none to minimal prgramming skills so it would be very helpful if someone has some tips or even code for that.

I'm working with excel 2007.

Re: Selection

LisaJermann — Tue, 25 Nov 2014 15:04:47 GMT

that sounds manageable. I think in some sort of you can do that with excel to because you have the (i don't know they are called) tables and could put in each table the sightings for each year and so on.

But of course that would be much easier in SQL because of the created views.

So thanks very much for your help

Re: Selection

JorgeOrellana — Tue, 25 Nov 2014 15:07:31 GMT

No problem, just keep in mind if you are going to maintain the excel route, that if you want multiple people access the spreadsheet at once you will run into issues.

Re: Selection

LisaJermann — Wed, 26 Nov 2014 10:38:55 GMT

Wow that hepled a lot. Thank you very much for your effort.

That sounds like the best way.

So the hole thing is a little bit tricky. The association don't have somedbody for running the GIS, they need it just now and then for some nice maps for their reports and their researches once a year. So they acctualy have no licence and I'm just writing my bachelor thesis about how to solve their problems and so on.

So I have a 1 year licence for my bachelor thesis. There will be none multiple access to the GIS till now and I think in the next future also not.

With the database it is worse. The association in Germany gets the hole database from the canary islands and they added just manually from sheets they wrote on the sightings into excel. So their will be no multiple users for arc Gis nore the excel sheet.

After my Bachelor thesis I will transfer them my ArcGIS "data" or project and the next intern will maybe do something with it and just want to update the database and then the new data should be sorted out in layers for new year, species and so on.

So I know their is a lot of controversial things going on their but I just get this task from my professor and should solve it some way. I think that is the best way because a nother task for the bachelor thesis is a database connection and so I think that would work great with the model builder.

Maybe now it is a little bit clearer what my or the problem of the association is because of the questions that I should ask me

Thank you so much for your help

Re: Selection

CattyannCampbell — Mon, 08 Dec 2014 19:03:48 GMT

Thanks

for passing on the model shots