Selection

[LisaJermann]

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.

[JorgeOrellana]

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.

[LisaJermann]

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 ?

[JorgeOrellana]

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

[LisaJermann]

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

[JorgeOrellana]

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.

[LisaJermann]

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.

[LisaJermann]

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. 

[CattyannCampbell]

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?

[LisaJermann]

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.