Query language for more than one type of value from one attribute

4874
3
09-29-2014 05:50 PM
DanielBrenner
Occasional Contributor


Yes this is almost equal to ArcGIS101 or Beginning SQL but I want to think ArcGIS does this better than Geomedia.   I will ultimately have to do the same in Geomedia but it is even harder to figure out how to do it in one step there so I just want to start simple.  If any of you know Geomedia and what query I'd  need to do there it would be mega-appreciated.

I have a roads layer that intersects polygons.  The polygons have two attributes with more than one type of value that designate the ID Num berfor the polygon and the type of polygon it is (Rural or Urban).   All I want to do is select all of the roads that have BOTH types of values, as in:

SELECT Roads FROM ROADS LAYER WHERE

Field1 = "xxxxx" and Field2 = "yyyyy"

How hard does it have to be to select two different types of values in relation to another?

I was trying to select by a string of characters identifying the County and the Jurisdiction Name but not the road specifically, and then the two fields with the combination of values.  Maybe it was because I didn't end the string of characters right? 

I've attached 2 jpgs of the data set and the query I was using which I ultimately had to give up on.   When I did try to do this in Geomedia, I had to create two shapefiles (equivalent of), one of which was the set of roads that touched the boundary of the polygon and another set of all the roads that touched that set as well, and then I joined them together.   I'd like to think I wouldn't have to do this.

Thanks for understanding.

Dan B

0 Kudos
3 Replies
DanPatterson_Retired
MVP Emeritus

Firstly, what you are querying will determine a lot of things.  For example, if you are querying a shapefile's table, then the wildcard options are % and _, the % meaning anything (ie anything after and anything before) where as the _ means that position can be occupied by anything.

Secondly, that query could use round brackets to ensure that the queries between and's and/or or's are carried out in the correct sequence.  If you could copy and paste the query so that it is text into your question, then it would be text and people could edit it...right now, you can't copy off a graphic

0 Kudos
DanielBrenner
Occasional Contributor

Dan:

Per your request to see the queries I've tried (with round brackets),,  I have just tried all of these and have gotten nothing or "an Invalid statement was used....".   I also tried a query where "csu_lrs_ke" = 'KIN_AVNL*' thinking that the * was what would represent a wild card character (any street name) , but that didn't work either.

I also have a similar sitaution where I need to query out all the segments that either start in an Urban area and END in a Rural one, or Start in a Rural Area and end in an Urban one, but do not go in and out again.   I have a sense that a similar query might need to be used, but again,  will also need to translate this into Geomedia language.   Yes I may bend over and just deal with Geomedia, but ArcGIS is more comfy.  (Or so I though).

If you could also tell me how I attach data to a reply that would be nice too.

"csu_lrs_ke" = 'KIN_AVNL_' AND "FirstOfCla" = 'Urban' AND 'Rural'

or

"csu_lrs_ke" = 'KIN_AVNL_' AND (("FirstOfCla" = 'Urban') AND ("FirstOfCla" = 'Rural'))

or

"csu_lrs_ke" = 'KIN_AVNL_%' AND {("FirstOfCla" = 'Urban') AND ("FirstOFCla" = 'Rural')}

0 Kudos
DanPatterson_Retired
MVP Emeritus

As I indicated in the previous comment, the nature of the query may be dependent upon what it is you are querying...

here is the SQL reference

attaching things is even stranger...normally, the Attach  (bottom right beside @ Mention) doesn't appear for me until I comment on a thread and then go back and edit it...if appears there for you all the time, then use it.  Obviously it would make more sense to have it on the editing bar.  So if you have a shapefile zip it and post it with a generic version of the query you want to perform.

But first, as an example, shapefiles use " " to enclose field names and ' ' to enclose what is queried. so

Field1 = "xxxxx" and Field2 = "yyyyy"  as a variant on your first example should be

("Field1" = "xxxxx") OR ("Field1" = "yyyyy" )

("Field1" = "xxxxx") AND ("Field2" = "yyyyy" )

etc etc

0 Kudos