====================================================================================================================================================================================================== == OCCURRANCES ========== ====================================================================================================================================================================================================== https://records-ws.nbnatlas.org/occurrences/search?q=lsid%3ANBNSYS0000003805&fq=-occurrence_status%3A%22absent%22&fq=(cl18%3A%22SY08%22%20OR%20cl18%3A%22SY09%22%20OR%20cl18%3A%22SY18%22%20OR%20cl18%3A%22SY19%22%20OR%20cl18%3A%22SY28%22%20OR%20cl18%3A%22SY29%22%20OR%20cl18%3A%22SY39%22) https://records-ws.nbnatlas.org/occurrences/search?q=lsid:NBNSYS0000003805&fq=-occurrence_status:"absent"&fq=(cl18:"SY08" OR cl18:"SY09" OR cl18:"SY18" OR cl18:"SY19" OR cl18:"SY28" OR cl18:"SY29" OR cl18:"SY39") ====================================================================================================================================================================================================== https://records-ws.nbnatlas.org/occurrences/search?q=lsid%3ANBNSYS0000003805%20AND%20cl188%3A%22Dorset%20Environmental%20Records%20Centre%22&fq=-occurrence_status%3A%22absent%22 https://records-ws.nbnatlas.org/occurrences/search?q=lsid:NBNSYS0000003805 AND cl188:"Dorset Environmental Records Centre"&fq=-occurrence_status:"absent" ====================================================================================================================================================================================================== https://records-ws.nbnatlas.org/occurrences/search?q=lsid%3A%22NHMSYS0000457892%22&fq=cl131%3A%22West%20Dorset%20District%22&fq=-occurrence_status%3Aabsent ====================================================================================================================================================================================================== https://records-ws.nbnatlas.org/occurrences/search?q=taxa:"Mind-Your-Own-Business"&fq=cl131:"West Dorset District"&fq=-occurrence_status:absent https://records-ws.nbnatlas.org%2Foccurrences%2Fsearch%3Fq%3Dtaxa%3A%22Mind-Your-Own-Business%22%26fq%3Dcl131%3A%22West%20Dorset%20District%22%26fq%3D-occurrence_status%3Aabsent ====================================================================================================================================================================================================== == AUTOCOMPLETE ========== ====================================================================================================================================================================================================== https://records-ws.nbnatlas.org/autocomplete/search?q=%22"+str+"%22&all=true&limit=500&pageSize=100 https://records-ws.nbnatlas.org/autocomplete/search?q=%22vulpes vulpes%22&fq=&all=true https://records-ws.nbnatlas.org/occurrences/search?q=&fq= https://species.nbnatlas.org/search?q=Badger&fq=rank:%22species%22&includeSynonyms=on ===================================================================================================== localhost:3306/c9804689_nature/t_Observations/ https://shcp12.hosting.zen.net.uk:2083/cpsess5930448830/3rdparty/phpMyAdmin/index.php?route=/table/sql&db=c9804689_nature&table=t_Observations Showing rows 0 - 7 (8 total, Query took 0.0043 seconds.) [recorder: ... - THELMA WEBSTER...] [Name: ... - ...] SELECT DISTINCT o.`memberID`, o.`recorder`, `m`.`Name`, m.`IDNo` AS `mIDNo` FROM `t_Observations` AS o LEFT JOIN Members AS m ON o.`recorder` LIKE m.`Name` WHERE `m`.`IDNo` IS NULL ORDER BY o.`recorder`, m.Name; memberID recorder Name mIDNo NULL NULL NULL NULL NULL Don & Shirley Morley NULL NULL = Shirley Morley (IDNo 33. Updated Membership Name) NULL Emma Berry NULL NULL = Emma Tracey (IDNo 13. Updated Membership Name) NULL Geoffrey Smith NULL NULL = Geoff Smith (IDNo 37. Updated Membership Name) NULL Kathleen Goodhart NULL NULL = Kathleen (IDNo 5. Updated Membership Name - SINCE DECEASED) NULL Jim and Karen NULL NULL = Jim Smyllie (IDNo 21) OR Karen Heaney (IDNo 23) NULL Joanna Jacobson NULL NULL = No record NULL Thelma Webster NULL NULL = No record ================================================================================================== UPDATE `t_Observations` AS o LEFT JOIN Members AS m ON o.`recorder` LIKE m.`Name` SET o.`memberID`= m.`IDNo` WHERE m.`IDNo` IS NOT NULL ================================================================================================== localhost:3306/c9804689_nature/columns/ https://shcp12.hosting.zen.net.uk:2083/cpsess1830659517/3rdparty/phpMyAdmin/index.php?route=/database/sql&db=c9804689_nature Showing rows 0 - 12 (13 total, Query took 0.0008 seconds.) [column_name: ADDITIONAL INFO... - VERIFY...] select column_name ,max(case when table_name = 'Observations' then 'Yes' end) as in_Observations ,max(case when table_name = 't_Observations' then 'Yes' end) as in_t_Observations from information_schema.columns where (table_name in('Observations', 't_Observations') and table_schema = 'c9804689_nature') group by column_name order by column_name; column_name in_Observations in_t_Observations ----------- --------------- ----------------- additional info Yes Yes date Yes Yes IDNo Yes Yes lat NULL Yes location Yes Yes lon NULL Yes memberID NULL Yes os grid reference Yes Yes recorder Yes Yes scientific name Yes Yes species Yes Yes type Yes Yes verify NULL Yes ================================================================================================== SELECT ROUND(AVG(`value`), 2) AS "avg", COUNT(`value`) AS "count", MIN(`date`) AS "from", MAX(`date`) AS "to", ROUND(MIN(`value`), 2) AS "lowest", ROUND(MAX(`value`), 2) AS "highest" FROM `t_Rainfall` WHERE `quality` LIKE "Good" AND MONTH(`date`) = MONTH(CURRENT_DATE) 3.69 456 2009-01-01 2024-01-31 0.00 37.00 https://experience.arcgis.com/experience/b2d9eb5680834a93ae3999663eb54b4e/page/Page/?views=Discharges%2CMore-about%2CSite-Detail-View#data_s=id%3AdataSource_7-18ed1843b9a-layer-9~dataSource_8-18ed165af8b-layer-12%3A612%2Cid%3AdataSource_8-18ed165af8e-layer-14%3A2781 ================================================================================================== UPDATE `Members` SET `Temp`= LOWER(REPLACE(REPLACE(REVERSE(SUBSTRING(`Email`, -18, 12)), ".", "&"), "@", "%")) WHERE `Hash` IS NULL SELECT LOWER(REPLACE(REPLACE(REVERSE(SUBSTRING(`Email`, -18, 12)), ".", "&"), "@", "%")) FROM `Members` WHERE `IDNo` = 1; SELECT REPLACE( REPLACE( REPLACE( LOWER( REPLACE( REPLACE( REVERSE( SUBSTRING( LPAD(`Email`, 18, "#") , 1, 12) ) , ".", "&") , "@", "%") ) , "a", "1") , "e", "5") , "o", "7") AS `ting`, `Name`, `Email` FROM `Members` WHERE 1 ============================================================================================= INSERT INTO `t_ghcensus` (`SchedNo`, `Address`, `Name`, `Relate`, `Marital`, `Occupation`, `BirthParish`, `Year`, `District`, `Sched`, `Age`) SELECT `SchedNo`, `Address`, `Name`, `Relate`, `Marital`, `Occupation`, `BirthParish`, `Year`, `District`, `Sched`, `Age` FROM `1911 ting` INSERT INTO `t_ghcensus` (`SchedNo`, `Address`, `Name`, `Relate`, `Marital`, `Occupation`, `BirthParish`, `Year`, `District`, `Sched`, `Age`) SELECT `SchedNo`, `Address`, `Name`, `Relate`, `Marital`, `Occupation`, `Birth Place`, `Year`, `District`, `Sched`, `Age` FROM `1921 ting` UPDATE `1921 ting` SET `Address`= REPLACE(`Address`, ", Dorset", "") WHERE 1; UPDATE `1921 ting` SET `Address`= REPLACE(`Address`, ", Dorset", "") WHERE CHAR_LENGTH(`Address`) > 36 UPDATE `1921 ting` SET `Age` = REPLACE(`Age`, " Years", " y") WHERE `Age` LIKE "%Years%"; UPDATE `1921 ting` SET `Age` = REPLACE(`Age`, " Month", "m") WHERE `Age` LIKE "% Month%"; UPDATE `1921 ting` SET `Age` = CONCAT(`Age`, "y") WHERE `Age` REGEXP '^[0-9]+$'; SELECT COUNT(*) AS `Rows`, `Address` FROM `1921 ting` WHERE `Address` LIKE "%Dorset%" GROUP BY `Address` ORDER BY `Rows` DESC, `Address` ASC; SELECT COUNT(*) AS `Rows`, `Address` FROM `1921 ting` WHERE (`Address` LIKE "%Dorset%") AND (CHAR_LENGTH(`Address`) > 36) GROUP BY `Address` ORDER BY `Rows` DESC, `Address` ASC; ========================================================================================== britmonitor.php: https://services.arcgis.com/3SZ6e0uCvPROr4mS/arcgis/rest/services/Wessex_Water_Storm_Overflow_Activity/FeatureServer/0/query?f=json&objectIds=711&outFields=Id%2CLastUpdated%2CLatestEventEnd%2CLatestEventStart%2CReceivingWaterCourse%2COBJECTID%2CStatus%2CStatusStart&outSR=27700&returnM=true&returnZ=true&spatialRel=esriSpatialRelIntersects&where=1%3D1 edm_harvest.php: https://services.arcgis.com/3SZ6e0uCvPROr4mS/arcgis/rest/services/Wessex_Water_Storm_Overflow_Activity/FeatureServer/0/query?f=json&objectIds=711&outFields=LatestEventStart%2CStatus%2CStatusStart&outSR=27700&returnM=true&returnZ=true&spatialRel=esriSpatialRelIntersects&where=1%3D1 t_edm_harvest.php: https://services.arcgis.com/3SZ6e0uCvPROr4mS/arcgis/rest/services/Wessex_Water_Storm_Overflow_Activity/FeatureServer/0/query?where=Id%20%3D%20%27WSX0505%27&outFields=StatusStart,LatestEventStart,LatestEventEnd,Latitude,ReceivingWaterCourse,LastUpdated,Status,Id&returnGeometry=false&outSR=4326&f=json