tutorial dekho queries
DESCRIPTION
Slides to accompany this post: http://www.dekho.com.au/understanding-queries-and-saying-goodbye/TRANSCRIPT
![Page 1: Tutorial dekho queries](https://reader033.vdocuments.us/reader033/viewer/2022051313/54842e8ab4af9fb55d8b4640/html5/thumbnails/1.jpg)
Dekho queries
![Page 2: Tutorial dekho queries](https://reader033.vdocuments.us/reader033/viewer/2022051313/54842e8ab4af9fb55d8b4640/html5/thumbnails/2.jpg)
Agenda
• Introduction to Dekho queries
• Query types and how to use them
• Spatially enabled queries
• Best practices and considerations
![Page 3: Tutorial dekho queries](https://reader033.vdocuments.us/reader033/viewer/2022051313/54842e8ab4af9fb55d8b4640/html5/thumbnails/3.jpg)
Complete the picture… with queries..
![Page 4: Tutorial dekho queries](https://reader033.vdocuments.us/reader033/viewer/2022051313/54842e8ab4af9fb55d8b4640/html5/thumbnails/4.jpg)
Dekho queries • Pull information from data sources
– Internal / External – Database / Web service / Map service
• Defined in Dekho Studio • Executed in runtime by end user
• Require some SQL skills
• Reusable between applications
![Page 5: Tutorial dekho queries](https://reader033.vdocuments.us/reader033/viewer/2022051313/54842e8ab4af9fb55d8b4640/html5/thumbnails/5.jpg)
Query sources
• JDBC connection
• Esri ArcGIS Server Map Service (GDB Only)
• Web Service
![Page 6: Tutorial dekho queries](https://reader033.vdocuments.us/reader033/viewer/2022051313/54842e8ab4af9fb55d8b4640/html5/thumbnails/6.jpg)
Query types • Searching
– Search
– Combo + Type-Ahead
• Result viewing
– Layer Information Query
– Feature Info Query
• Others
– Call out
– Integration
1
2
3
![Page 7: Tutorial dekho queries](https://reader033.vdocuments.us/reader033/viewer/2022051313/54842e8ab4af9fb55d8b4640/html5/thumbnails/7.jpg)
![Page 8: Tutorial dekho queries](https://reader033.vdocuments.us/reader033/viewer/2022051313/54842e8ab4af9fb55d8b4640/html5/thumbnails/8.jpg)
Defining a Search Query • Always executed against a feature class in the GDB
• Returning a single spatial feature set (ObjectID)
• User interaction – data entry fields (optional)
Select * from [Feature Class] Where [User data entry]
Select * from properties Where street_name like %Manningtree%
![Page 9: Tutorial dekho queries](https://reader033.vdocuments.us/reader033/viewer/2022051313/54842e8ab4af9fb55d8b4640/html5/thumbnails/9.jpg)
User data entry fields • Go into the “Where
clause” of the search SQL statement
• Text / Date / List / Drop Down
![Page 10: Tutorial dekho queries](https://reader033.vdocuments.us/reader033/viewer/2022051313/54842e8ab4af9fb55d8b4640/html5/thumbnails/10.jpg)
Type-ahead Combo-box
![Page 11: Tutorial dekho queries](https://reader033.vdocuments.us/reader033/viewer/2022051313/54842e8ab4af9fb55d8b4640/html5/thumbnails/11.jpg)
Combo and Type-ahead • Structure user data entry in search queries
• ANY database (not just the GDB)
• Lists Of Values (LOV): – Value = What you display
– ID = What you use
• Ordered (? Your call…)
ID Value
Vic Victoria
NSW New South Wales
Tas Tasmania
WA West Australia
SA South Australia
NT Northern Territory
![Page 12: Tutorial dekho queries](https://reader033.vdocuments.us/reader033/viewer/2022051313/54842e8ab4af9fb55d8b4640/html5/thumbnails/12.jpg)
Search #2
Reusing Combo and Type-ahead
• Define once and reuse
Combo-Box Query
Search #1 Search #3
![Page 13: Tutorial dekho queries](https://reader033.vdocuments.us/reader033/viewer/2022051313/54842e8ab4af9fb55d8b4640/html5/thumbnails/13.jpg)
![Page 14: Tutorial dekho queries](https://reader033.vdocuments.us/reader033/viewer/2022051313/54842e8ab4af9fb55d8b4640/html5/thumbnails/14.jpg)
Working with search results • Working with a complete picture
• Connecting with non-spatial data sources
• Getting the information you need
ObjectID ObjectID
ObjectID ObjectID
ObjectID ObjectID
ObjectID ObjectID
![Page 15: Tutorial dekho queries](https://reader033.vdocuments.us/reader033/viewer/2022051313/54842e8ab4af9fb55d8b4640/html5/thumbnails/15.jpg)
LIQ’s and FIQ’s
Layer Information
Specific feature Information
![Page 16: Tutorial dekho queries](https://reader033.vdocuments.us/reader033/viewer/2022051313/54842e8ab4af9fb55d8b4640/html5/thumbnails/16.jpg)
Getting the information you need
Asset Mgmt System
Asset ID
GDB
Current Selected Feature
The information I wish to display
![Page 17: Tutorial dekho queries](https://reader033.vdocuments.us/reader033/viewer/2022051313/54842e8ab4af9fb55d8b4640/html5/thumbnails/17.jpg)
Getting the information you need
ObjectID AssetID
AID
Asset Mgmt System
Asset ID
GDB ObjectID
Foreign Asset Id KEY
AssetID
![Page 18: Tutorial dekho queries](https://reader033.vdocuments.us/reader033/viewer/2022051313/54842e8ab4af9fb55d8b4640/html5/thumbnails/18.jpg)
GDB link to 3rd party systems
![Page 19: Tutorial dekho queries](https://reader033.vdocuments.us/reader033/viewer/2022051313/54842e8ab4af9fb55d8b4640/html5/thumbnails/19.jpg)
Getting the information you need
ObjectID AssetID
AID X Y Z
What Dekho does: 1. Get AssetId by ObjectID (GDB)
2. Find row in AMS table based on AssetID
3. Select relevant fields (X,Y,Z)
![Page 20: Tutorial dekho queries](https://reader033.vdocuments.us/reader033/viewer/2022051313/54842e8ab4af9fb55d8b4640/html5/thumbnails/20.jpg)
Getting the data you need
Asset Mgmt System
Asset ID
GDB
ObjectID+AssetID
Mapping relationship SELECTION.AssetID = AMS.AssetID
![Page 21: Tutorial dekho queries](https://reader033.vdocuments.us/reader033/viewer/2022051313/54842e8ab4af9fb55d8b4640/html5/thumbnails/21.jpg)
LIQ’s and FIQ’s
Multiple queries span across multiple tabs
![Page 22: Tutorial dekho queries](https://reader033.vdocuments.us/reader033/viewer/2022051313/54842e8ab4af9fb55d8b4640/html5/thumbnails/22.jpg)
Example: reviewing a property
Owners history list
Related approvals
Lot # information
FIQ1 FIQ2 FIQ3
![Page 23: Tutorial dekho queries](https://reader033.vdocuments.us/reader033/viewer/2022051313/54842e8ab4af9fb55d8b4640/html5/thumbnails/23.jpg)
Queries and maps
![Page 24: Tutorial dekho queries](https://reader033.vdocuments.us/reader033/viewer/2022051313/54842e8ab4af9fb55d8b4640/html5/thumbnails/24.jpg)
Query types • Searching
– Search
– Combo + Typeahead
• Result viewing
– Layer Information Query
– Feature Info Query
• Others
– Call out
– Integration
![Page 25: Tutorial dekho queries](https://reader033.vdocuments.us/reader033/viewer/2022051313/54842e8ab4af9fb55d8b4640/html5/thumbnails/25.jpg)
Callout Query
• Any data source
• Select relevant fields
![Page 26: Tutorial dekho queries](https://reader033.vdocuments.us/reader033/viewer/2022051313/54842e8ab4af9fb55d8b4640/html5/thumbnails/26.jpg)
Dekho Integration Query
• SQL query that translates one set of values (OBJECTID) to another (ASSETID)
![Page 27: Tutorial dekho queries](https://reader033.vdocuments.us/reader033/viewer/2022051313/54842e8ab4af9fb55d8b4640/html5/thumbnails/27.jpg)
Dekho Integration Query
![Page 28: Tutorial dekho queries](https://reader033.vdocuments.us/reader033/viewer/2022051313/54842e8ab4af9fb55d8b4640/html5/thumbnails/28.jpg)
Dekho Integration Query
![Page 29: Tutorial dekho queries](https://reader033.vdocuments.us/reader033/viewer/2022051313/54842e8ab4af9fb55d8b4640/html5/thumbnails/29.jpg)
Query types • Searching
– Search
– Combo + Typeahead
• Result viewing
– Layer Information Query
– Feature Info Query
• Others
– Call out
– Integration
![Page 30: Tutorial dekho queries](https://reader033.vdocuments.us/reader033/viewer/2022051313/54842e8ab4af9fb55d8b4640/html5/thumbnails/30.jpg)
Spatial selection
I want users to search a heritage site and see properties
that intersect with the selected heritage site
![Page 31: Tutorial dekho queries](https://reader033.vdocuments.us/reader033/viewer/2022051313/54842e8ab4af9fb55d8b4640/html5/thumbnails/31.jpg)
Spatial selection
Heritage site layer
Property lot layer
Spatial query
Search query
![Page 32: Tutorial dekho queries](https://reader033.vdocuments.us/reader033/viewer/2022051313/54842e8ab4af9fb55d8b4640/html5/thumbnails/32.jpg)
Queries points for consideration
![Page 33: Tutorial dekho queries](https://reader033.vdocuments.us/reader033/viewer/2022051313/54842e8ab4af9fb55d8b4640/html5/thumbnails/33.jpg)
Remember!! • Spatial query is optional
– Search query
– Feature Info query
• Results are from the TARGET layer
![Page 34: Tutorial dekho queries](https://reader033.vdocuments.us/reader033/viewer/2022051313/54842e8ab4af9fb55d8b4640/html5/thumbnails/34.jpg)
Queries points for consideration • Renaming DB fields names to logical names
select Field_Name1 as “User Friendly Name1”
• Complete the picture vs. Information overload – Avoid select * statements
– Use multiple FIQ’s and LIQ’s
– Map to end user workflow
• Query performance optimization
![Page 35: Tutorial dekho queries](https://reader033.vdocuments.us/reader033/viewer/2022051313/54842e8ab4af9fb55d8b4640/html5/thumbnails/35.jpg)
Complete the picture… with queries..
![Page 36: Tutorial dekho queries](https://reader033.vdocuments.us/reader033/viewer/2022051313/54842e8ab4af9fb55d8b4640/html5/thumbnails/36.jpg)
Product Information:
www.esriaustralia.com.au/dekho
www.esriaustralia.com.au/dekhohelp
www.esriaustralia.com.au/dekhoblog