Re: [MI-L] SQL Querying
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 Units sq km' statement in the MapBasic window beforehand. To find proportion overlap, use the proportionoverlap(table1.obj,table2.obj) function instead, but note that the proportions returned will be those of the objects from the first table listed in the function (table1). Andrew Andrew Tracey wrote: I have two tables - 1 consisting of 70 polygons and 1 consisting of 20 polygons. Both tables cover the same area, what I want to do is work out what area or percentage of each of the polygons that is within another. Does anyone know the syntax to do this? Thanks Andrew Tracey Information Support Officer (GIS) Information Team Chief Executive Office South Tyneside Council Westoe Road South Shields NE33 2RL Tel: 0191 4247561 E-Mail : [EMAIL PROTECTED] Please do not print this e-mail if you can help it - and help protect the environment. This email and any files transmitted with it are confidential and intended solely for the named recipient. If you have received this e-mail in error, please immediately notify the sender by e-mail and delete from your system, you are not permitted to disseminate, store, disclose or copy. E-mail transmission cannot be guaranteed to be secure or error-free as they could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses and therefore the Council accept no liability for any such errors or omissions. Views or opinions expressed in this email are solely those of the author and do not necessarily represent those of the Council.South Tyneside Council, Town Hall Civic Offices, Westoe Road, South Shields, Tyne Wear, NE33 2RL, Tel: 0191 427 1717, Website: www.southtyneside.info The Council's web site address is www.southtyneside.info ___ MapInfo-L mailing list MapInfo-L@lists.directionsmag.com http://www.directionsmag.com/mailman/listinfo/mapinfo-l -- Andy Harfoot GeoData Institute University of Southampton Southampton SO17 1BJ Tel: +44 (0)23 8059 2719 Fax: +44 (0)23 8059 2849 www.geodata.soton.ac.uk --- For further information about GeoData's Training Courses, please visit: www.geodata.soton.ac.uk/training --- ___ MapInfo-L mailing list MapInfo-L@lists.directionsmag.com http://www.directionsmag.com/mailman/listinfo/mapinfo-l
RE: [MI-L] SQL Querying
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 A/S Odensevej 95 DK-5260 Odense S. Denmark Tel +45 6311 4900 Direct +45 6311 4908 Mob +45 5156 1045 Fax +45 6311 4949 E-mail [EMAIL PROTECTED] http://www.cowi.dk/gis For enden af regnbuen... - hvordan kommer man dertil og er det overhovedet muligt? Læs mere om årets MapInfo konference på www.cowi.dk/mapinfokonference -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Andrew Tracey Sent: Monday, September 18, 2006 5:17 PM To: MapInfo-L@lists.directionsmag.com Subject: [MI-L] SQL Querying I have two tables - 1 consisting of 70 polygons and 1 consisting of 20 polygons. Both tables cover the same area, what I want to do is work out what area or percentage of each of the polygons that is within another. Does anyone know the syntax to do this? Thanks Andrew Tracey Information Support Officer (GIS) Information Team Chief Executive Office South Tyneside Council Westoe Road South Shields NE33 2RL Tel: 0191 4247561 E-Mail : [EMAIL PROTECTED] Please do not print this e-mail if you can help it - and help protect the environment. This email and any files transmitted with it are confidential and intended solely for the named recipient. If you have received this e-mail in error, please immediately notify the sender by e-mail and delete from your system, you are not permitted to disseminate, store, disclose or copy. E-mail transmission cannot be guaranteed to be secure or error-free as they could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses and therefore the Council accept no liability for any such errors or omissions. Views or opinions expressed in this email are solely those of the author and do not necessarily represent those of the Council.South Tyneside Council, Town Hall Civic Offices, Westoe Road, South Shields, Tyne Wear, NE33 2RL, Tel: 0191 427 1717, Website: www.southtyneside.info The Council's web site address is www.southtyneside.info ___ MapInfo-L mailing list MapInfo-L@lists.directionsmag.com http://www.directionsmag.com/mailman/listinfo/mapinfo-l ___ MapInfo-L mailing list MapInfo-L@lists.directionsmag.com http://www.directionsmag.com/mailman/listinfo/mapinfo-l
RE: [MI-L] SQL Querying
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: MapInfo-L@lists.directionsmag.com Subject: [MI-L] SQL Querying Dear All I have a table containing 6000+ points which has a code of 1-12 associated with each point, as well as a boundary table, I want to get a count of points within each boundary dependant on code (1-12). This is easy to do but I would like the results to be displayed in a table format e.g.: - Code1 Code2 Code3 etc Area1 200 300 400 Area2 100 200 300 Area3 400 500 600 Etc Does anyone know if this is possible? Thanks in Advance Andrew Tracey Information Support Officer (GIS) Information Team Chief Executive Office South Tyneside Council Westoe Road South Shields NE33 2RL Tel: 0191 4247561 E-Mail : [EMAIL PROTECTED] ___ MapInfo-L mailing list MapInfo-L@lists.directionsmag.com http://www.directionsmag.com/mailman/listinfo/mapinfo-l
RE: [MI-L] SQL Querying
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 it so its probably full of bugs. 'begin code 'Make sure you have packed the squared_regions table 'The following will get all the values you want into an array. From there you can push them into another table Type matrix bottomleftx as float bottomlefty as float toprightx as float toprighty as float thecentroid as float id as integer End Type Dim squaresarray(1) as matrix Dim i as integer Dim thisobj as object Select * from squared_regions Fetch First from Selection For i = 1 to SelectionInfo(Sel_info_Nrows) thisobj = Selection.obj redim squaresarray(i) squaresarray(i).bottomleftx = ObjectGeography(thisobj,Geo_Min_X) squaresarray(i).bottomlefty = ObjectGeography(thisobj,Geo_Min_Y) squaresarray(i).toprightx = ObjectGeography(thisobj,Geo_Max_X) squaresarray(i).toprighty = ObjectGeography(thisobj,Geo_Max_Y) squaresarray(i).thecentroid = Centroid(thisobj) squaresarray(i).id = Selection.A'assuming A is the id column in your squared_regions table Fetch next from Selection Next ' at this point you would create a new table and extract the values from the array into it ' optionally you could directly populate another table by altering the code above to do so 'end code 'good luck, ' Steve -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Andrew Tracey Sent: Tuesday, May 02, 2006 4:24 AM To: MapInfo-L@lists.directionsmag.com Subject: [MI-L] SQL Querying Dear All I have a MapInfo table, which consists of 200+ squared regions. I want to query to find out the bottom left X,Y coordinate and the top Right X,Y coordinate, plus a centroid and the id number from column A, does anyone know if this is possible. Thanks in advance Andrew Tracey Information Support Officer Information Team Chief Executive Office South Tyneside Council Westoe Road South Shields NE33 2RL Tel: 0191 4247561 E-Mail : [EMAIL PROTECTED] Please do not print this e-mail if you can help it - and help protect the environment. This email and any files transmitted with it are confidential and intended solely for the named recipient. If you have received this e-mail in error, please immediately notify the sender by e-mail and delete from your system, you are not permitted to disseminate, store, disclose or copy. E-mail transmission cannot be guaranteed to be secure or error-free as they could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses and therefore the Council accept no liability for any such errors or omissions. Views or opinions expressed in this email are solely those of the author and do not necessarily represent those of the Council.South Tyneside Council, Town Hall Civic Offices, Westoe Road, South Shields, Tyne Wear, NE33 2RL, Tel: 0191 427 1717, Website: www.southtyneside.info The Council's web site address is www.southtyneside.info ___ MapInfo-L mailing list MapInfo-L@lists.directionsmag.com http://www.directionsmag.com/mailman/listinfo/mapinfo-l __ This e-mail has been scanned by Verizon/MessageLabs Managed Email Service. __ This e-mail has been scanned by Verizon/MessageLabs Managed Email Service. ___ MapInfo-L mailing list MapInfo-L@lists.directionsmag.com http://www.directionsmag.com/mailman/listinfo/mapinfo-l
RE: MI-L SQL Querying
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 of a way of querying for all non-matched entries between 2 different tables? I have two tables with postcode in and want to find which don't match. Thanks Andrew Tracey - List hosting provided by Directions Magazine | www.directionsmag.com | To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] Message number: 18188
RE: MI-L SQL Querying
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 Tracey [mailto:[EMAIL PROTECTED] Sent: 30 November 2004 17:54 To: [EMAIL PROTECTED] Subject: MI-L SQL Querying Dear All I am trying to select all records within a column that have the tenth character as either a 2 or a 3. All of the records within the column have 13 numbers. Is there any easy way to produce a query for the above? Regards Andrew Tracey Information Support Officer Corporate Information Corporate Growth and Focus Corporate Development South Tyneside Council Westoe Road South Shields NE33 2RL Tel: 0191 4247561 E-Mail : [EMAIL PROTECTED] Please do not print this e-mail if you can help it - and help protect the environment. This Message may contain confidential information and is protected by copyright. If you receive it in error please notify us and delete it without making use of or copying it. The addressee and other employees within the Council may read and copy any e-mail reply to this message and other e-mails you send to us. Whilst we use virus checking procedures we accept no liability for viruses and recipients must rely on their own virus checking procedures. The Council's web site address is www.southtyneside.info - List hosting provided by Directions Magazine | www.directionsmag.com | To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] Message number: 14291 __ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email __ - List hosting provided by Directions Magazine | www.directionsmag.com | To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] Message number: 14292
RE: MI-L SQL Querying
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 Consultant ESBI Computing Ltd Stephen Court | 18-21 St. Stephen's Green | Dublin 2 Tel +353 1 7038060 | Fax +353 1 6618455 E-mail: [EMAIL PROTECTED] www.esbic.ie -Original Message- From: Andrew Tracey [mailto:[EMAIL PROTECTED] Sent: 30 November 2004 17:54 To: [EMAIL PROTECTED] Subject: MI-L SQL Querying Dear All I am trying to select all records within a column that have the tenth character as either a 2 or a 3. All of the records within the column have 13 numbers. Is there any easy way to produce a query for the above? Regards Andrew Tracey Information Support Officer Corporate Information Corporate Growth and Focus Corporate Development South Tyneside Council Westoe Road South Shields NE33 2RL Tel: 0191 4247561 E-Mail : [EMAIL PROTECTED] Please do not print this e-mail if you can help it - and help protect the environment. This Message may contain confidential information and is protected by copyright. If you receive it in error please notify us and delete it without making use of or copying it. The addressee and other employees within the Council may read and copy any e-mail reply to this message and other e-mails you send to us. Whilst we use virus checking procedures we accept no liability for viruses and recipients must rely on their own virus checking procedures. The Council's web site address is www.southtyneside.info - List hosting provided by Directions Magazine | www.directionsmag.com | To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] Message number: 14291 * ** *** ** * ** *** ** * ** *** ** * This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. Any views or opinions presented are solely those of the author, and do not necessarily represent those of ESB. If you have received this email in error please notify the sender. Although ESB scans e-mail and attachments for viruses, it does not guarantee that either are virus-free and accepts no liability for any damage sustained as a result of viruses. * ** *** ** * ** *** ** * ** *** ** * - List hosting provided by Directions Magazine | www.directionsmag.com | To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] Message number: 14293
RE: MI-L SQL Querying
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: Andrew Tracey [mailto:[EMAIL PROTECTED] Sent: 20 October 2004 14:08 To: [EMAIL PROTECTED] Subject: MI-L SQL Querying Dear All I have a gazetteer table in MapInfo and I am trying to write a query to extract the following : There are two columns which I require data from porptype and propsit. Both columns are populated with 1 of 5-10 letters. I need to select every different value from proptype and then count every different propsit for that proptype. So the results will look like :- ProptypePropsit D E F G A 123 456 789 101 B 101 102 103 104 I hope this makes some sense. Any ideas would be appreciated. Andrew Tracey Information Support Officer Corporate Information Corporate Growth and Focus Corporate Development South Tyneside Council Westoe Road South Shields NE33 2RL Tel: 0191 4247561 E-Mail : [EMAIL PROTECTED] Please do not print this e-mail if you can help it - and help protect the environment. This Message may contain confidential information and is protected by copyright. If you receive it in error please notify us and delete it without making use of or copying it. The addressee and other employees within the Council may read and copy any e-mail reply to this message and other e-mails you send to us. Whilst we use virus checking procedures we accept no liability for viruses and recipients must rely on their own virus checking procedures. The Council's web site address is www.southtyneside.info - List hosting provided by Directions Magazine | www.directionsmag.com | To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] Message number: 13781 This message has been scanned for viruses by MailControl - (see http://bluepages.wsatkins.co.uk/?4318150) This email and any attached files are confidential and copyright protected. If you are not the addressee, any dissemination of this communication is strictly prohibited. Unless otherwise expressly agreed in writing, nothing stated in this communication shall be legally binding. - List hosting provided by Directions Magazine | www.directionsmag.com | To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] Message number: 13783
RE: MI-L SQL Querying
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] 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 anybody have a SQL that will do this ? Thanks in advance Andrew Tracey Development Services Department South Tyneside M.B.C. Town Hall Westoe Road South Shields Tyne Wear NE33 2RL 0191-4271717 Ext. 7561 E-Mail : [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify your system manager. South Tyneside Metropolitan Borough Council www.s-tyneside-mbc.gov.uk ** stmbc-ms-disclaim - List hosting provided by Directions Magazine | www.directionsmag.com | To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - List hosting provided by Directions Magazine | www.directionsmag.com | To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: MI-L SQL Querying
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 anybody have a SQL that will do this ? Thanks in advance Andrew Tracey Development Services Department South Tyneside M.B.C. Town Hall Westoe Road South Shields Tyne Wear NE33 2RL 0191-4271717 Ext. 7561 E-Mail : [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify your system manager. South Tyneside Metropolitan Borough Council www.s-tyneside-mbc.gov.uk ** stmbc-ms-disclaim - List hosting provided by Directions Magazine | www.directionsmag.com | To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - List hosting provided by Directions Magazine | www.directionsmag.com | To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
