Hi Andrew,
The SQL Select syntax is:
select table1.IDcol , table2.IDcol , areaoverlap(table1.obj,table2.obj)
from table1,table2
where table1.obj intersects table2.obj
Note that the area returned will be in Mapinfo's current area units, so
you might want to set these by running the 'Set Area
Andrew,
This should do it:
Select TABLE1.NAME
, CartesianArea(Overlap( TABLE1.OBJ, TABLE2.OBJ), sq m)
AREA_SQM
FromTABLE1, TABLE2
Where TABLE1.OBJ Intersects TABLE2.OBJ
Peter Horsbøll Møller
GIS Developer, MTM
Geographical Information IT
COWI
Select areaname, sum(int(mycode=code1))Code1,
sum(int(mycode=code2))Code2, ...
From mybigtab
Group by areaname
Hope this helps
Spencer
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Andrew
Tracey
Sent: Tuesday, August 15, 2006 11:51 AM
To:
You need to separate the criteria in the WHERE clause
Select * from table1, table2 WHERE table1.obj within table2.obj and table1.id =
[some value here] and table2.id = [some value here]
Ben Greenberg
GIS Coordinator
NAI MLG Commercial
262-797-9400
-Original Message-
From: [EMAIL
Andrew,
There is probably a procedural/manual way of doing this via a series of
manual selections using geographic functions in your selections. A
better was is via mapbasic. Here is some mapbasic to get you started if
mapbasic is an option for you.
Disclaimer:I just typed it out and have not run
-
From: Robert DeRubeis
[mailto:[EMAIL PROTECTED]
Sent: Friday, 14 April 2006 1:43
AM
To:
mapinfo-l@lists.directionsmag.com
Subject: Re: [MI-L] sql select
That's what the problem was. Table A was not mappable
and Table B was mappable. I made Table A mappable and ran the same query
and it selected
That's what the problem was. Table A was not mappable and Table B was mappable. I made Table A mappable and ran the same query and it selected the records from Table A.
Thanks for the help.
-Bob
Mike Mayer [EMAIL PROTECTED] on Thursday, April 13, 2006 at 9:46 AM -0700 wrote:
Are the
Hi Peter
- add the new column as float
- run table / update column and specifiy colxx-771.49
Mit freundlichem Gruss / Best Regards
Flavio Hendry
TYDAC NEWS http://www.tydac.ch/german/index.php?menu=News_actual
Andrew,
I would recommend either building a MapBasic application or just using a
Workspace to perform this task.
You only need to know three MapBasic commands to perform this:
Open Table path and name of table
Server Refresh name of table
Close Table name of table
This can very easy by put
Andrew
lnRandNum = round( rnd(1) * 79000, 1) will give you a random no. 1 -
79000
Fetch record lnRandNum will get that record for you .
I don't know about every 100 or every 1000 records because I don't
know what that means. Do you mean every 100th ... (after a random
record number) ... or a
Hi Andrew
You've probably had a number of replies already but here goes
To select every nth record from a table using SQL, type
rowid mod(n)=0
... into the Where Condition area of the dialog.
Hope this makes sense and is of some assistance.
Regards
Tony Maber - Certified
Hi,
This will select every 100th record:
Select * From MYTABLE
Where ROWID Mod 100 = 0
This will select every 1000th record:
Select * From MYTABLE
Where ROWID Mod 1000 = 0
You can also change the 0 to any value between 0 and 100 or 1000.
Peter Horsbøll Møller
GIS Developer, MTM
Geographical
Andrew,
There's 2 options both requiring the addition of a new column (as float) to
your table that is then updated.
Version 1 is to update the new column with the rowid number thus:
Update Mytable Set Mycol = rowid
and then
Select * from Mytable where Mycol mod N into
andy
you dont say if you just want a query to do this or an update (answers so far
have been update based.. which is ok if you have a column ready to fill..if
noy then you have teh hassle of altering teh table structure etc etc..
I tend to do this kinda stuff as a query so setup the QL
Andrew,
- Goto Table Update Column
- Select the table you want to update
- Select the column to update
- In the value field you enter this: COLUMNNAME1 * COLUMNNAME2
where COLUMNNAME1 is the name of the first column and COLUMNNAME2 is
the name of the second table
Peter Horsbøll Møller
Update MYTABLE SET MYCOLPRODUCT = MYCOL1 * MYCOL2
If you have a large dataset you might first what to run the following
line in the mapbasic window:
Set Table MYTABLE FastEdit On Undo Off
HTH,
Jakob Lanstorp
Software Consultant
Geographical Information IT
COWI A/S
Odensevej 95
DK-5260 Odense
Hi Andrew,
Refreshing is only needed to update a local copy of the table on MS/SQL.
The local copy is in MapInfo TAB format, and unless you can utilize MITAB to
extract the data, you need MapInfo Pro.
Best regards/Med venlig hilsen
Lars V. Nielsen
GisPro, Denmark
http://www.gispro.dk/
-
Hi Bill
You can use the syntax in the Where Condition:
SUBURB = Any (Alpha,Tango,Charlie,Whisky, ) .
If your list is not too long then this way should work.
Should you have a lengthy list ...
it might be worth typing the names into an Excel spreadsheet and then importing
it into MI and
Thanks to Hugh, Peter and Jose for the help, looks like a matter of having
all your ducks in a row :)
-Original Message-
Objects in one table = text objects some line objects
Objects in second table = region/polyg objects
Attempting to select objects in one table that are Not Within
Andrew
Select Postcode
from Table 1
where PostCode not in ( select Postcode from Table2)
HTH
Terry McDonnell
-Original Message-
From: Andrew Tracey [mailto:[EMAIL PROTECTED]
Sent: 10 October 2005 15:01
To: Terry McDonnell
Subject: MI-L SQL Querying
Dear All
Does anyone know
Hi David,
Sure, it is doable. It's just a question of putting the words in the right
order ;-)
And I also think that you need to use a subselect for this query:
Select * From ONETABLE
Where Not OBJ Within Any (Select Obj From SECONDTABLE)
Note that I have placed the Not in front of the
Yep that got it working.
Thanks
Mike
Terry McDonnell [EMAIL PROTECTED] wrote:
Mike
I haven't done this myself yet but does the below work?
HTH
Terry
Select *
from Table2
where table2.obj within Buffer( table1.Obj, 1, 100, m)
-Original Message-
From: Mike Tonge [mailto:[EMAIL
Hendrik,
1.
There isn't a HG_SpericalBuffer or ST_Buffer UDF. See the following notes.
HG_SphericalBuffer generates a buffer region around a geometry in a
longitude/latitude coordinate system.
Note: This function is available within sp_spatial_query, but not available as
a UDF, because SQL
PM
To: Terry McDonnell; mapinfo-l@lists.directionsmag.com
Subject: RE: MI-L SQL selects
Yep that got it working.
Thanks
Mike
Terry McDonnell [EMAIL PROTECTED] wrote:
Mike
I haven't done this myself yet but does the below work?
HTH
Terry
Select *
from Table2
where table2.obj within Buffer
Hi David,
Sum() is an aggregate function that sums exactly one column.
Try using: ( sum(a)+sum(b)+sum(c) ) / sum(d)
Best regards/Med venlig hilsen
Lars V. Nielsen
GisPro, Denmark
http://www.gispro.dk/
- Original Message -
From: Dewen Hou [EMAIL PROTECTED]
To: Mapinfo-L
Hi Group,
Thanks to all those who replied about the Universal Translator. I am going to
wait and try to use MapInfo 7.8 on my other workstation, where the files were
originally created, instead of the 7.0 verion which is what I've been trying to
translate with. Hopefully it works!
My next
: woensdag 17 augustus 2005 23:50
To: Lars V. Nielsen (GisPro); Dewen Hou
Cc: Mapinfo-L
Subject: RE: MI-L SQL question
Hi Group,
Thanks to all those who replied about the Universal Translator. I am going
to wait and try to use MapInfo 7.8 on my other workstation, where the files
were originally created
Hi Roger,
We've run SpatialWare (4.5) on MS/SQL 2000 from both MIPro and MapXtreme 3.0
(MapX 5.0) without too many problems. And we're definitely not using sa to
access MS/SQL from MXT either.
It may be a security/privilege problem you're dealing with. If the tables
are owned by dbo, sa will
Philip
If your Shield nodes are identified numbered sequentially, e.g. ShieldNo,
beginning at 1, then you could probably do:
Select * from ShieldLayer where ShieldLayer.ShieldNo MOD 5 = 0
That should get every 5th shield, so every 5 miles.
HTH
Terry McDonnell
-Original Message-
Someone who knows more than I about SQL might have a way of specifying *
from one table only. Absent that, I have a workaround for you.
I'm assuming that you're using two tables because of the need to use one of
them to help select records -- e.g. selecting records where the FIPS code in
Table1
Sorry, for UDFs that seems to be 2 single quotes - see the manual, Chapter 6
(page 31).
Ian Thomas
GeoSciSoft - Perth, Australia
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Thursday, 13 January 2005 8:54 PM
To: MapInfo-L@lists.directionsmag.com
Subject:
/ctuzoning/index.htm
-Original Message-
From: Will Mitchell [mailto:[EMAIL PROTECTED]
Sent: Monday, January 10, 2005 1:14 PM
To: [EMAIL PROTECTED]; MapInfo-L@lists.directionsmag.com
Subject: RE: MI-L SQL question
You can reverse the order of the tables in the SQL dialog, should return
You can reverse the order of the tables in the SQL dialog, should return the
first table's geography/polygon...
Will Mitchell
Mitchell Geographics, Inc.
496 Congress St
Portland, ME 04101
ph 207.879.7769
fx 207.253.5756
www.mitchellgeo.com
-Original Message-
From: [EMAIL PROTECTED]
This is the query you need
Mid$(Column_name,10, 1) = 2 Or Mid$(Column_name,10, 1) = 3
Syntax:
Mid$( str, num1, num2 )
Returns a portion of the string str starting at character position num1
and extending for num2 characters.
Hope it works
Cheers
Tim
-Original Message-
From:
Andrew,
Try something like:
'Select * from Untitled where Mid$(ID,10,1) = 2 or Mid$(ID,10,1) =
3 into Selection'
For Chars
'Select * from Untitled where Mid$(Str$(ID),10,1) = 2 or
Mid$(Str$(ID),10,1) = 3 into Selection'
For Decimals
Enjoy,
Jerry
Jerry O'Sullivan
GIS
hi willie
table / update column, use rowid as value.
Mit freundlichem Gruss / Best Regards
Flavio Hendry
TYDAC NEWS http://www.tydac.ch/german/index.php?menu=News_actual
Andrew,
Use the SQL query form then GROUP BY PropType, PropSit ORDER BY PropType,
PRopSit
This will give you
A,D,123
A,E,456
..
B,D,101
Etc.
Un less you really need the matrix form I reckon this is the best way to do
it.
Else I'll suggest Mapbasic.
Cheers
Erik
-Original Message-
From:
Sergi
Select blah from blah where blah into selection (or into csrMyQuery)
Select blah from selection (or from csrMyQuery) where ...
Terry
-Original Message-
From: Gamiz Ribelles, Sergi (Regsa) [mailto:[EMAIL PROTECTED]
Sent: 20 July 2004 14:55
To: Mapinfo-list (E-mail)
Subject: MI-L
The Mod keyword might be what you need.
i.e.
SELECT * FROM ContourTable WHERE ElevationColumn MOD 5 = 0
This will return all values of Elevation that can be divided exactly by 5.
Hope that helps,
Dominic
-Original Message-
From: Véronique De Laet [mailto:[EMAIL PROTECTED]
Sent: 17
Sorry, missed Bill's answer!
-Original Message-
From: Bill Thoen [mailto:[EMAIL PROTECTED]
Sent: 17 July 2004 22:18
To: Véronique De Laet
Cc: [EMAIL PROTECTED]
Subject: Re: MI-L sql select problem
On Sat, 17 Jul 2004, Véronique De Laet wrote:
We have a database with elevation values
On Sat, 17 Jul 2004, Véronique De Laet wrote:
We have a database with elevation values of contourlines with an
aquidistance of 1m. Now we want to select lines with an interval of
5m.
If you want to select contour lines with elevation values that are even
multiples of 5m, you can do so
Hi Nick
One book I use a lot - which is not GIS oriented is Oracle9i: SQL, with
an Introduction to PL/SGL.
It is obviously Oracle oriented; however, it is very comprehensive.
Here is a link to some info on the book =
http://www.course.com/catalog/product.cfm?isbn=0-619-06475-7
I hope it
SQL in a nutshell (O'Rielly) is a good starter. ISBN:1-56592-744-3
Cheers
Upinder
-Original Message-
From: Nick Hall [mailto:[EMAIL PROTECTED]
Sent: Wednesday, 2 June 2004 8:02 PM
To: MapInfo-L
Subject: MI-L SQL books
anybody know any good SQL books (both GIS and non-GIS would be
I find myself adding this AND clause to just about any spatial query I use-
Region may also be Line, Pline, etc, however, region is most common
way to use it. The table it needs to reference is the one with the objects
you are using to select with, not needed for the objects of the target
table.
str$(obj) for a polyline returns Polyline, not Pline.
In addition, there is a line object type for which str$(obj)
returns line.
You can use either of the two expressions below to search for
rows that are neither lines nor polylines:
not (str$(obj)=any (line, polyline))
instr(1,str$(obj),line)
Hello Soren,
You've fallen into a common trap when learning SQL! Sometimes AND's and OR's
are the opposite to what one would express in spoken language. In this case,
I assume you want to select records from any of the three cities. In this
case you need to use OR instead of AND. What's happening
Hi Soren
Map Info does not want And if you are selecting from one field, but it rather wants
Or. So simple run the same query and put Or instead of And.
Regards,
Norman Mabunda
Manager: GIS and Databases
[EMAIL PROTECTED]
+27 12 312 0843 (Tel)
+27 12 312 0642 (fax)
+27 73 265 9279
Thanks to those who answered my simple query.
Answer is you can't do this directly in the update command. Just
perform the required selection and update from that!, as from Martin
Higham:
Select * from table where colA = num1 and col2 = num2 Update
selection set colC = num3
-Original
Hi Robin,
first you need to do a bit of SQL to get the counts of crime types
POSTCODETABLE = Postcode boundary table name
CRIMETYPETABLE = Crime type table name
Select POSTCODETABLE.POSTCODE, CRIMETYPETABLE.CRIMETYPE, Count(*)
From POSTCODETABLE, CRIMETYPETABLE
Group by 1,2
Order by 1,2
You
I have a table that I wanted to do a similiar thing to what Andrew Tracey wanted to do.
Please see the attachment to see what I did:
Select Columns: Province, Type, Count(Sub_Type) 5
From Tables:National_Health_Facility_List_
Where Condition:
Group By Columns: Sub_Type
, 5260 Odense S.
Please note our new telephone, direct and telefax numbers.
-Original Message-
From: Norman Mabunda [mailto:[EMAIL PROTECTED]
Sent: Thursday, January 15, 2004 8:14 AM
To: [EMAIL PROTECTED];
[EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: Re: MI-L SQL Query
I have
.
Please note our new telephone, direct and telefax numbers.
-Original Message-
From: Norman Mabunda [mailto:[EMAIL PROTECTED]
Sent: Thursday, January 15, 2004 8:14 AM
To: [EMAIL PROTECTED];
[EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: Re: MI-L SQL Query
I have a table that I
To do that, you will have to add a field called ID, update it to contain
rowid (i.e. 1, 2, 3,) and then include a where clause in your SQL:
where rowid=20
or
where rowid=20 and rowid=100
If you want to see only the top 20 records in the output (i.e. after the
group by), then the answer is
PROTECTED]
Subject: RE: MI-L SQL Query
Thanx Peter
Another question: Is't possible 1000 records count to see
only a certail number of records. For instance, of the 100
records count, to see the 1st 20 records, or to see only 20
records of the 100 records. If yes, how?
Regards
Very much answered. Thanks a mil.
Uffe Kousgaard [EMAIL PROTECTED] 01/15/04 09:59AM
To do that, you will have to add a field called ID, update it to contain
rowid (i.e. 1, 2, 3,) and then include a where clause in your SQL:
where rowid=20
or
where rowid=20 and rowid=100
If you want to see
:45 AM
To: Peter Horsbøll Møller; [EMAIL PROTECTED]
Subject: RE: MI-L SQL Query
Thanx Peter
Another question: Is't possible 1000 records count to see
only a certail number of records. For instance, of the 100
records count, to see the 1st 20 records, or to see only 20
records
Try this:
Select * From MYTABLE Where MYCOLUMN Like %Richard%
or
Select * From MYTABLE Where InStr(1, MYCOLUMN, Richard) 0
Replace:
MYTABLE with the name of your table
MYCOLUMN with the name of the Company Name column
I'm not which is fastest, but they should do the same thing. Note that the %
Yes, the query would go something like this:
Select WARD_NO, TENURE_TYPE, Count(TENTURE_TYPE) How_Many
From MY_TABLE
GROUP BY WARD_NO, TENURE_TYPE
ORDER BY WARD_NO, TENURE_TYPE
Into RESULTS_TABLE
If you are using the SQL select dialog, the first line above is the
'Select Columns', the second
To: [EMAIL PROTECTED];
[EMAIL PROTECTED]
Subject: Re: MI-L SQL Query
Yes, the query would go something like this:
Select WARD_NO, TENURE_TYPE, Count(TENTURE_TYPE) How_Many
From MY_TABLE GROUP BY WARD_NO, TENURE_TYPE ORDER BY WARD_NO,
TENURE_TYPE Into RESULTS_TABLE
If you are using
I was wandering what MI (7.5) does to Oracle (9.2) when querying a table in the
direct mode.
Do you perform this query using the SQL Select or do you send the query to Oracle
using the DBMS expert ?
If you send the query to Oracle you can use the power of Oracle to get a much better
Hi Carolyn,
There's surely a way to do this using the / as a separator, but in my
limited ways I use the Left$() function. Trick is you have to have the same
number of places each time, so if your numbers grow you'll have to do it
multiple times on fixed width subsets.
You can update your other
Carolyn,
Use the Instr function in combination with the Left$ function
Instr(1,123/456,/) returns 4, which is the chanacter position of the
/
So,
Update TableName Set FirstPart = left$(Data,InStr(1,Data,/)-1)
will update the column FirstPart in table Tablename with everything
before the first
Carolyn,
Use the table Update Column menu item and set it up so that you use
this expression to update your new field.
Left$(OldField,Instr(1,/,OldField)-1)
r
On Tue, 9 Dec 2003 12:31:15 +1100, Carolyn Bergin
[EMAIL PROTECTED] wrote:
Hi guys
I have a dataset that contains a field with
note our new telephone, direct and telefax numbers.
COWI wish you a merry Christmas and a happy New Year.
-Original Message-
From: Will Mitchell [mailto:[EMAIL PROTECTED]
Sent: Tuesday, December 09, 2003 2:58 AM
To: Carolyn Bergin; [EMAIL PROTECTED]
Subject: RE: MI-L SQL Help
as far as i'm aware a JOIN requires the same fundamental data type, a WHERE
statement does not, so long as they're processed as the same data type.
confuserating i know, but a JOIN is interpreted slightly differently in SQL,
it is more of a hardcoded response where it can take advantage of
I can only give a partial answer here...
I don't have time too look up the answer on the MapInfo side.
SQL Server: nvarchar
This and other character data types with an n prefix are UNICODE data
types. This causes two things to occur. The length of the data type
storage has some restrictions,
MapInfo's SQL isn't as robust as we'd like, so I think you'll
have to do this in two passes. First select data1,data2
intersections into a table, then the data1, data3 intersections,
and combine the results.
--
- Bill Thoen
GISnet,
Hi Don,
I have not seen a limit on the number of tables that can be used when
joining tables but I have read about the order in which they have to be
joined.
This is what I read:
The order of the table names (in the From Tables field) is important. If
both tables contain map objects, the results
Wade,
Maybe you should take a look at http://www.w3schools.com/sql/. It is not
MapInfo specific, but it should help you with SQL grammar a little bit. Hope
this helps.
Happy Hollidays everyone!!
Milton Fonseca
Research Analyst I
Mineral Resources and
Mineral Hazards Mapping Program
California
I believe the problem is with base tables verses a named query. Try this
SQL which operates on the 2 base tables, USA which contains the state
objects and UsCuty_8k for the cities, or use any other city table you want.
Select * from Usa, Uscty_8k where Usa.State = WY AND Usa.obj Contains
Hi Seth,
No such thing in MapInfo SQL. It's clearly a subset of the full SQL query set.
However, as opposed to standard SQL, all your queries in MIPro are permanent - in your
session or until you close them - and with some restrictions can be used as basis for
further queries. You can name
Following on from my previous post, the error message that MapInfo could not
close a temp table is due to a bug.
If you open a shapefile directly using MI Pro v7 and then make a selection
from it, you get this message when attempting to close the temp table. Note
that this will cause unexpected
The REAL error. Ok, i packed the tables and that didn't help. And my error actually
says this Object not inserted. What the heck is THAT and what does it mean? And
why is it happenning? More importantly, how can I fix it? I am using MI Pro
6.5/WinXP.
-Evan MacDougall ([EMAIL PROTECTED])
Depending on your version of MapInfo, the MapBasic reference guide, which
details all of these functions, is included in PDF format on the MapInfo
install disk.
I have MapInfo 6.5 and the file is here: d:\pdf_docs\mb_ref.pdf
Tim Nuteson
Target Corporation
-Original Message-
From:
I use this site http://www.w3schools.com/sql/ for reference. Hopefully, it
helps.
Qiaomei
-Original Message-
From: Jeff Card [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, September 18, 2002 1:00 PM
To: [EMAIL PROTECTED]
Subject: MI-L SQL
Can anyone give me an idea where I could find an
]
ema.gov cc: 'MI-List'
[EMAIL PROTECTED]
Vedr.: RE: MI-L SQL
18-09-2002
It is done in a 2 step process:
set table mytable fastedit on
select * from mytable where myfield = myvalue into tmp1 noselect
delete [object] from tmp1
Perhaps even like this (not tested):
set table mytable fastedit on
select * from mytable where myfield = myvalue
delete [object] from
Do remember to set the right projection before selecting the records with
SQL:
1. Open the MapBasic window
2. Enter this followed by a Enter: Set CoordSys Table name of table
name of table is the name of your table with the correct projection
3. Do the SQL select.
HTH,
Peter
Hi Andrew,
SELECT MyCol
FROM MyTAB
WHERE Len(MyCol) 40
Hope this helps,
Michael Hanna
Industrial Engineering
United Parcel Service Canada Ltd.
(905) 660-8688
-Original Message-
From: Andrew Tracey [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, July 09, 2002 9:11 AM
To: [EMAIL PROTECTED]
I believe it's len(columnname) 40
Dan
Andrew Tracey [EMAIL PROTECTED] on 07/09/2002 09:10:51
AM
To:[EMAIL PROTECTED]
cc:
Subject:MI-L SQL Querying
Dear All
Can anybody help I am trying to pick out all records within a column that
are more than 40 characters in length. Does
Nick,
I believe that the structure you want cannot be achieved using a query. The
best result you can get from a query is:
Region1 Tile1
Region1 Tile2
Region1 Tile3
Region2 Tile1
Region2 Tile3...
You can, however, use this resulting table to generate a
Jacques Paris [EMAIL PROTECTED]
Sendt af: [EMAIL PROTECTED]
09-03-2001 20:58
Besvar venligst til Jacques Paris
Til:Morrier, Steve [EMAIL PROTECTED],
[EMAIL PROTECTED]
cc:
Vedr.: RE: MI-L SQL Select
I used once in the SQL requester that where condition
instr(1,Col_Name
Andrew,
From the Query, SQL Select option type:
Select Columns: *
from Tables: your_table_name
Where: str$(objectinfo(obj,1)) = n
with n representing one of the following numbers:
1 - arc
2 - ellipse/circle
3 - line
4 - polyline
5 - point
6 - layout window frame
7 - region
8 -
Str$(obj) = text is much simpler
-Original Message-
From: Driver, Greg 9434 [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, 26 June 2002 4:55 PM
To: 'Andrew Tracey'; '[EMAIL PROTECTED]'
Subject: RE: MI-L SQL Query
Andrew,
From the Query, SQL Select option type:
Select Columns: *
from
-04-2002 03:16 Vedr.: RE: MI-L SQL query to group data
on classifications
Seems like you have 90% of it figured out. I'll assume your
table is called MyTable and the field where you are storing
your group sums is MyField [as a Char(4)]. If you take your
last expression (where you get TFFF for up to 1,000,
TTFF for 1,000 to 5,000, TTTF for 5,000 to 10,000, and
The only way I can think of is to add a column, say GroupPos, populate it,
and query where GroupPos = 3. If your table is large you can populate it
quickly be writing a short MabBasic application. I'd be glad to help with
this if you need.
Michael Hanna
-Original Message-
From: Ron
Go to Table Update Column and fill in the appropriate fields:
If you open a mapbasic window first, the result should look like this:
Add Column polygontable (countfield) From pointtable Set To Count(*)
Where within
Kind regards
Uffe Kousgaard
www.routeware.dk
- Original Message -
Jeff,
The key is the order that you list the tables in the table box of the SQL
select. The first table will hold a bunch of selected records. In your case
make sure you list the points table first, then the data table. The
selected records are the same records that show in the query, but
Steve,
Not to worry, you won't get fired. Remember we are the ones who actually do
the work!
Here is a method for selecting records in one table that do not match
records in another table.
Consider a situation where we must find all the records in TableA whose
values in FieldA do not match the
, 2002 12:13 PM
To: 'Morrier, Steve'; MapInfo-L (E-mail)
Subject: RE: MI-L SQL Help
Steve,
Not to worry, you won't get fired. Remember we are the ones who actually do
the work!
Here is a method for selecting records in one table that do not match
records in another table.
Consider a situation
:
onsmag.com Vedr.: RE: MI-L SQL Help
List
Subject: RE: MI-L SQL
Thanks to everyone else that helped out on this problem.
The summary
Invert selection does not work after you have made an SQL Query.
Select * from points where not obj within any (select obj from polygon) into
Selection (Applying this calculation made MI crash out
Stephen
If you have MiPro 6.5, try selecting all the points within the poly and
then using the Inverse Selection Tool to select all other points.
Bill Arnold
Meridian Geographics
www.mgmap.com
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]] On Behalf Of Brooks,
Stephen,
Lets say you have a table with polygons and a tables with points
then the SQL is:-
Select * from points where not obj within any (select obj from polygon) into
Selection
However this runs really sloww on large tables...
So you if you are using Version 6.5 you could join the two
Hello Bill,
select COLUMN_NAME, DATA_TYPE from information_schema.columns where
TABLE_NAME = YourTable
There are lots of other useful field too. Should work like any other table
queried via ODBC.
Regards,
Warren Vick
Europa Technologies Ltd.
http://www.europa-tech.com
-Original
Soren,
I have experienced/discovered this too in the past, and have reported it
to MapInfo, they say it is due to the way that MI handles one to many
relations (sorry cant find the correspondence), but I think its just a
bug in the info tool!! or at least a design feature they need to sort
out.
Hi Søren,
I have experienced this problem too. As mad as I am that this bug has
existed for so long, I have just accepted it and used a work-around.
Save the new_table, then open it. The correct relationships will be
restored.
Cheers,
Simon
Hi,
Thought to just comment on the don't know how you'd join all three =
tables within a single SQL.
Joining 3 tables in a single SQL proves to be powerful, time saving and =
does not clutter your disk with unecessary temporary files. The way to =
do this requires a syntax rule which I will
Lorne,
1 - If you are sure that the end=start lines exist as lines, then you can
add as an extra condition that objectlen(obj,m) be 0 to exclude them
2 - The implementation by MI of the notion of centroid for lines and plines
is different from that for regions. One one hand, centroids of
1 - 100 of 118 matches
Mail list logo
