SQL Query not selecting all points that it should?Changing symbology of selected features only using ArcMap?SQL-query should return GeoJSON FeatureCollectionSelecting all cells not containing a number in SQLSql Query in PostgresqlHow to make a SQL query that would sum multiple records in ArcGIS?Link Excel Table to Access/Arcmap Personal Geodatabase TableSub-Query Using “NOT IN” (Selection Not Correct)Clipping large shapefile in ArcMap and .dbf size remains same?Selecting polygon feature classes with 1 record in ArcSDE using SQL query?Selecting records by SQL Query and saving SUM or COUNT result into differnt table
Packing rectangles: Does rotation ever help?
How could Tony Stark make this in Endgame?
What makes accurate emulation of old systems a difficult task?
A Strange Latex Symbol
How can I practically buy stocks?
What language was spoken in East Asia before Proto-Turkic?
Please, smoke with good manners
How to get a plain text file version of a CP/M .BAS (M-BASIC) program?
Don’t seats that recline flat defeat the purpose of having seatbelts?
Why do games have consumables?
Why does nature favour the Laplacian?
Error message with Tabularx
How to verbalise code in Mathematica?
Does Gita support doctrine of eternal cycle of birth and death for evil people?
Combinable filters
How does a program know if stdout is connected to a terminal or a pipe?
How to creep the reader out with what seems like a normal person?
How to have a sharp product image?
How much cash can I safely carry into the USA and avoid civil forfeiture?
How can Republicans who favour free markets, consistently express anger when they don't like the outcome of that choice?
US visa is under administrative processing, I need the passport back ASAP
Is this homebrew Wind Wave spell balanced?
How would one muzzle a full grown polar bear in the 13th century?
Does holding a wand and speaking its command word count as V/S/M spell components?
SQL Query not selecting all points that it should?
Changing symbology of selected features only using ArcMap?SQL-query should return GeoJSON FeatureCollectionSelecting all cells not containing a number in SQLSql Query in PostgresqlHow to make a SQL query that would sum multiple records in ArcGIS?Link Excel Table to Access/Arcmap Personal Geodatabase TableSub-Query Using “NOT IN” (Selection Not Correct)Clipping large shapefile in ArcMap and .dbf size remains same?Selecting polygon feature classes with 1 record in ArcSDE using SQL query?Selecting records by SQL Query and saving SUM or COUNT result into differnt table
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;
I am working on a map of 60+ monitoring wells and I plotted them through an excel data sheet and a few tools. In that data sheet, I have Benzene concentrations associated with almost each point. I need to have the ones that are above SHS to be green and the ones that are below SHS to be red. I have tried setting up SQL Query a few times and also tried selecting by attribute, but every time it only selects one or two points when it should be selecting 12 points (7, 17, 23, 25, 37, 58, 140, 180, 230, 270, 280, 1100), I've tried a few different queries including "Benzene" > '5', which selected 2 points (7 and 58), "Benzene" >= '7', which selected one point (7). I tried selecting by attribute and only 7 was selected every time. Also, when I've tried doing the opposite in label editor ( "Benzene" <= '5', it says it is a valid expression, but all the numbers above 5 are still showing up green).
What am I doing wrong?
My benzene column is a String.
arcgis-desktop arcmap sql fields-attributes query
edited Apr 24 at 13:05
PolyGeo♦
54.1k1782247
asked Apr 24 at 12:17
mv9916mv9916
111
New contributor
mv9916 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
I am working on a map of 60+ monitoring wells and I plotted them through an excel data sheet and a few tools. In that data sheet, I have Benzene concentrations associated with almost each point. I need to have the ones that are above SHS to be green and the ones that are below SHS to be red. I have tried setting up SQL Query a few times and also tried selecting by attribute, but every time it only selects one or two points when it should be selecting 12 points (7, 17, 23, 25, 37, 58, 140, 180, 230, 270, 280, 1100), I've tried a few different queries including "Benzene" > '5', which selected 2 points (7 and 58), "Benzene" >= '7', which selected one point (7). I tried selecting by attribute and only 7 was selected every time. Also, when I've tried doing the opposite in label editor ( "Benzene" <= '5', it says it is a valid expression, but all the numbers above 5 are still showing up green).
What am I doing wrong?
My benzene column is a String.
arcgis-desktop arcmap sql fields-attributes query
edited Apr 24 at 13:05
PolyGeo♦
54.1k1782247
asked Apr 24 at 12:17
mv9916mv9916
111
New contributor
mv9916 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
I am working on a map of 60+ monitoring wells and I plotted them through an excel data sheet and a few tools. In that data sheet, I have Benzene concentrations associated with almost each point. I need to have the ones that are above SHS to be green and the ones that are below SHS to be red. I have tried setting up SQL Query a few times and also tried selecting by attribute, but every time it only selects one or two points when it should be selecting 12 points (7, 17, 23, 25, 37, 58, 140, 180, 230, 270, 280, 1100), I've tried a few different queries including "Benzene" > '5', which selected 2 points (7 and 58), "Benzene" >= '7', which selected one point (7). I tried selecting by attribute and only 7 was selected every time. Also, when I've tried doing the opposite in label editor ( "Benzene" <= '5', it says it is a valid expression, but all the numbers above 5 are still showing up green).
What am I doing wrong?
My benzene column is a String.
arcgis-desktop arcmap sql fields-attributes query
edited Apr 24 at 13:05
PolyGeo♦
54.1k1782247
asked Apr 24 at 12:17
mv9916mv9916
111
New contributor
mv9916 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
I am working on a map of 60+ monitoring wells and I plotted them through an excel data sheet and a few tools. In that data sheet, I have Benzene concentrations associated with almost each point. I need to have the ones that are above SHS to be green and the ones that are below SHS to be red. I have tried setting up SQL Query a few times and also tried selecting by attribute, but every time it only selects one or two points when it should be selecting 12 points (7, 17, 23, 25, 37, 58, 140, 180, 230, 270, 280, 1100), I've tried a few different queries including "Benzene" > '5', which selected 2 points (7 and 58), "Benzene" >= '7', which selected one point (7). I tried selecting by attribute and only 7 was selected every time. Also, when I've tried doing the opposite in label editor ( "Benzene" <= '5', it says it is a valid expression, but all the numbers above 5 are still showing up green).
What am I doing wrong?
My benzene column is a String.
arcgis-desktop arcmap sql fields-attributes query
arcgis-desktop arcmap sql fields-attributes query
edited Apr 24 at 13:05
PolyGeo♦
54.1k1782247
asked Apr 24 at 12:17
mv9916mv9916
111
New contributor
mv9916 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
edited Apr 24 at 13:05
PolyGeo♦
54.1k1782247
asked Apr 24 at 12:17
mv9916mv9916
111
New contributor
mv9916 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
edited Apr 24 at 13:05
PolyGeo♦
54.1k1782247
edited Apr 24 at 13:05
PolyGeo♦
54.1k1782247
edited Apr 24 at 13:05
PolyGeo♦
54.1k1782247
54.1k1782247
asked Apr 24 at 12:17
mv9916mv9916
111
New contributor
mv9916 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked Apr 24 at 12:17
mv9916mv9916
111
asked Apr 24 at 12:17
mv9916mv9916
111
111
New contributor
mv9916 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
mv9916 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
mv9916 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
When comparing strings, the comparison is done character by character, so 2 is "bigger" - or rather "after" 12 (2 vs 1).
You would need to change the datatype in your expression
int("Benzene") >= 7
You might have to use CInt() instead of int()
answered Apr 24 at 12:28
JGHJGH
13.9k21339
I got an error: 'A name was started with an invalid character'
– mv9916
Apr 24 at 12:43
add a comment |
Your problem is the data type as String.
You are trying to use it like numerical value but in string the "number" order is "alphabetical" meaning that "2" for exemple come after 11 (or that 2 > 11 is evaluated as true...).
If you sort your column in the attribute table you will see that all number starting by "1" are on top (like 1, 10, 1125,...) then number starting by "2" then ...
As consequence you can't use operator like > or < with number set as string and expect a meaningful result.
The solution is to change the data type to numeric (either when you import your data or by creating a new field and use the field calculator to convert your string number to number number)
answered Apr 24 at 12:29
J.RJ.R
3,711222
Thank you! When I used the 'Make XY Event Layer' tool it automatically set it as a string. Didn't even think of that. Thank you!
– mv9916
Apr 24 at 12:30
Just realized why it's a string. Most of my values for Benzene are <1 and they won't work in a double or long integer
– mv9916
Apr 24 at 12:39
3
@mv9916, a Double should work with any number, doesn't matter if it has decimals or not.
– klewis
Apr 24 at 16:30
add a comment |
Your Answer
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "79"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
mv9916 is a new contributor. Be nice, and check out our Code of Conduct.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fgis.stackexchange.com%2fquestions%2f320722%2fsql-query-not-selecting-all-points-that-it-should%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
When comparing strings, the comparison is done character by character, so 2 is "bigger" - or rather "after" 12 (2 vs 1).
You would need to change the datatype in your expression
int("Benzene") >= 7
You might have to use CInt() instead of int()
answered Apr 24 at 12:28
JGHJGH
13.9k21339
I got an error: 'A name was started with an invalid character'
– mv9916
Apr 24 at 12:43
add a comment |
When comparing strings, the comparison is done character by character, so 2 is "bigger" - or rather "after" 12 (2 vs 1).
You would need to change the datatype in your expression
int("Benzene") >= 7
You might have to use CInt() instead of int()
answered Apr 24 at 12:28
JGHJGH
13.9k21339
I got an error: 'A name was started with an invalid character'
– mv9916
Apr 24 at 12:43
add a comment |
When comparing strings, the comparison is done character by character, so 2 is "bigger" - or rather "after" 12 (2 vs 1).
You would need to change the datatype in your expression
int("Benzene") >= 7
You might have to use CInt() instead of int()
answered Apr 24 at 12:28
JGHJGH
13.9k21339
When comparing strings, the comparison is done character by character, so 2 is "bigger" - or rather "after" 12 (2 vs 1).
You would need to change the datatype in your expression
int("Benzene") >= 7
You might have to use CInt() instead of int()
answered Apr 24 at 12:28
JGHJGH
13.9k21339
answered Apr 24 at 12:28
JGHJGH
13.9k21339
answered Apr 24 at 12:28
JGHJGH
13.9k21339
answered Apr 24 at 12:28
JGHJGH
13.9k21339
13.9k21339
I got an error: 'A name was started with an invalid character'
– mv9916
Apr 24 at 12:43
add a comment |
I got an error: 'A name was started with an invalid character'
– mv9916
Apr 24 at 12:43
I got an error: 'A name was started with an invalid character'
– mv9916
Apr 24 at 12:43
I got an error: 'A name was started with an invalid character'
– mv9916
Apr 24 at 12:43
add a comment |
Your problem is the data type as String.
You are trying to use it like numerical value but in string the "number" order is "alphabetical" meaning that "2" for exemple come after 11 (or that 2 > 11 is evaluated as true...).
If you sort your column in the attribute table you will see that all number starting by "1" are on top (like 1, 10, 1125,...) then number starting by "2" then ...
As consequence you can't use operator like > or < with number set as string and expect a meaningful result.
The solution is to change the data type to numeric (either when you import your data or by creating a new field and use the field calculator to convert your string number to number number)
answered Apr 24 at 12:29
J.RJ.R
3,711222
Thank you! When I used the 'Make XY Event Layer' tool it automatically set it as a string. Didn't even think of that. Thank you!
– mv9916
Apr 24 at 12:30
Just realized why it's a string. Most of my values for Benzene are <1 and they won't work in a double or long integer
– mv9916
Apr 24 at 12:39
3
@mv9916, a Double should work with any number, doesn't matter if it has decimals or not.
– klewis
Apr 24 at 16:30
add a comment |
Your problem is the data type as String.
You are trying to use it like numerical value but in string the "number" order is "alphabetical" meaning that "2" for exemple come after 11 (or that 2 > 11 is evaluated as true...).
If you sort your column in the attribute table you will see that all number starting by "1" are on top (like 1, 10, 1125,...) then number starting by "2" then ...
As consequence you can't use operator like > or < with number set as string and expect a meaningful result.
The solution is to change the data type to numeric (either when you import your data or by creating a new field and use the field calculator to convert your string number to number number)
answered Apr 24 at 12:29
J.RJ.R
3,711222
Thank you! When I used the 'Make XY Event Layer' tool it automatically set it as a string. Didn't even think of that. Thank you!
– mv9916
Apr 24 at 12:30
Just realized why it's a string. Most of my values for Benzene are <1 and they won't work in a double or long integer
– mv9916
Apr 24 at 12:39
3
@mv9916, a Double should work with any number, doesn't matter if it has decimals or not.
– klewis
Apr 24 at 16:30
add a comment |
Your problem is the data type as String.
You are trying to use it like numerical value but in string the "number" order is "alphabetical" meaning that "2" for exemple come after 11 (or that 2 > 11 is evaluated as true...).
If you sort your column in the attribute table you will see that all number starting by "1" are on top (like 1, 10, 1125,...) then number starting by "2" then ...
As consequence you can't use operator like > or < with number set as string and expect a meaningful result.
The solution is to change the data type to numeric (either when you import your data or by creating a new field and use the field calculator to convert your string number to number number)
answered Apr 24 at 12:29
J.RJ.R
3,711222
Your problem is the data type as String.
You are trying to use it like numerical value but in string the "number" order is "alphabetical" meaning that "2" for exemple come after 11 (or that 2 > 11 is evaluated as true...).
If you sort your column in the attribute table you will see that all number starting by "1" are on top (like 1, 10, 1125,...) then number starting by "2" then ...
As consequence you can't use operator like > or < with number set as string and expect a meaningful result.
The solution is to change the data type to numeric (either when you import your data or by creating a new field and use the field calculator to convert your string number to number number)
answered Apr 24 at 12:29
J.RJ.R
3,711222
answered Apr 24 at 12:29
J.RJ.R
3,711222
answered Apr 24 at 12:29
J.RJ.R
3,711222
answered Apr 24 at 12:29
J.RJ.R
3,711222
3,711222
Thank you! When I used the 'Make XY Event Layer' tool it automatically set it as a string. Didn't even think of that. Thank you!
– mv9916
Apr 24 at 12:30
Just realized why it's a string. Most of my values for Benzene are <1 and they won't work in a double or long integer
– mv9916
Apr 24 at 12:39
3
@mv9916, a Double should work with any number, doesn't matter if it has decimals or not.
– klewis
Apr 24 at 16:30
add a comment |
Thank you! When I used the 'Make XY Event Layer' tool it automatically set it as a string. Didn't even think of that. Thank you!
– mv9916
Apr 24 at 12:30
Just realized why it's a string. Most of my values for Benzene are <1 and they won't work in a double or long integer
– mv9916
Apr 24 at 12:39
3
@mv9916, a Double should work with any number, doesn't matter if it has decimals or not.
– klewis
Apr 24 at 16:30
Thank you! When I used the 'Make XY Event Layer' tool it automatically set it as a string. Didn't even think of that. Thank you!
– mv9916
Apr 24 at 12:30
Thank you! When I used the 'Make XY Event Layer' tool it automatically set it as a string. Didn't even think of that. Thank you!
– mv9916
Apr 24 at 12:30
Just realized why it's a string. Most of my values for Benzene are <1 and they won't work in a double or long integer
– mv9916
Apr 24 at 12:39
Just realized why it's a string. Most of my values for Benzene are <1 and they won't work in a double or long integer
– mv9916
Apr 24 at 12:39
3
3
@mv9916, a Double should work with any number, doesn't matter if it has decimals or not.
– klewis
Apr 24 at 16:30
@mv9916, a Double should work with any number, doesn't matter if it has decimals or not.
– klewis
Apr 24 at 16:30
add a comment |
mv9916 is a new contributor. Be nice, and check out our Code of Conduct.
mv9916 is a new contributor. Be nice, and check out our Code of Conduct.
mv9916 is a new contributor. Be nice, and check out our Code of Conduct.
mv9916 is a new contributor. Be nice, and check out our Code of Conduct.
Thanks for contributing an answer to Geographic Information Systems Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fgis.stackexchange.com%2fquestions%2f320722%2fsql-query-not-selecting-all-points-that-it-should%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
